I need help on writing a function that will cycle thru numbers of a field and allow the user to either round up or down in .5 increments.
For example: (18 is just a random number, it could be any number from 1-200)
18.1 - Round down to 18 or up to 18.5
18.2 - Round down to 18 or up to 18.5
18.3 - Round down to 18 or up to 18.5
18.4 - Round down to 18 or up to 18.5
18.5 - Stays the same
18.6 - Round down to 18.5 or up to 19
18.7 - Round down to 18.5 or up to 19
18.8 - Round down to 18.5 or up to 19
18.9 - Round down to 18.5 or up to 19
I am trying to write a query, it needs to multiply 2 numbers from separate columns, then, I need it to Round the value UP to the nearest 0.25? Does anyone know how to write this formula in a query?
I am having some problems with rounding numbers. I have a DB using forms and i want the user to enter a weight and it rounds to the nearest half KG, so either ?.5 or ?.0 depending on what the number is. I know i can enter a range check in the table validation or say => 5 AND <= 10 but i am stuck on the rounding validation. I think this must be enterd into the text box of the form but as to what the syntax would be i am lost.
I have the following expression in a query. This gets the order due date takes away todays date then goes through a series of calculations to work out the period in terms of a number of months (Assuming they are all 4 weeks, wrong I know)
((((([Blanket_One]![Due Date]-Date())/7))*5)/20)
My problem is fairly simple I think. I need the output from this expression to always round up to the nearest whole number.
Example (1.0009 would round up to 2)
In excel I can use the function Roundup, is there an equivalent in Access?
I have a form with a field that gives me an average of results. Below the number 10 it works fine - for example, if I have 6.3 as an average it displays 6, if it is 6.7 it displays 7.
If however the number is greater than 10 I have a problem. Should the number contain a decimal it rounds down to 10 or up to 20. ie any number below 14.9 will round down to 10 and any number above 15.1 will round up to 20. Whole numbers display fine.
I have a field that holds a unit price. I can't use currency as the data type because the currency type is different for each client. I have another field that specifies the currency type for each department. Up to now I've been using a double format with a 2 decimal places. We now need to increase the decimal places to 4 but don't necessarily want to see all four places if only 2 are required - 1.5000 against 1.50. However if I make the decimals as auto, if I have 1.50 it rounds to 1.5.
Any other way to give a bit of flexibility to this?
Am sure there is a simple solution to the people that know.....which isn't me! But I have a text box on a report to calculate % of total and don't know how to Round it up or display the total?
The Text box is:
=([TotalTemp])/([GrandTotal])*100
Tried =Round([TotalTemp])/([GrandTotal])*100 & "%" with no joy and have quite a few of these to implement.
I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.
example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.
I am trying to get Access to round numbers to the third decimal place and I can only get it to round up to the nearest whole number. I have used the help function and tried several things but can not get ACCESS to round as I need it. Any HELP is greatly appreciated!! Thanks, DOUG
Hoping someone can help. I have looked for an answer and can't quite grasp what I need to do. I am using Access 2000.
Basically, I have a quote form that adds together all job related services and totals them using a currency field. Sales would like to have the option of quoting the job two decimal places or going out to three. So, I need to round to the two different cost options. I can't figure out how to do this. So, my question is:
1) Am I using the correct data type? 2) If currency is most appropriate, how do I show results such as: $12.327 and $12.33.
If I only change decimal property (therefore, the display) for the fields, the form shows: 12.33 and 12.330. Have tried using the Round function different data types and not having any luck.
This hopefully is an easy question for you guys :)
My problem is this...I have a lot of code on a command button. One portion is it divides one field by another field. this works correctly; however, it is rounding stuff and I'm unsure what to do about it...here are the examples:
100/10=10 88/10=9 97/10=10
That are the results I am getting when the function is ran...are there some settings on my table that are incorrect? both are set to double / number with decimal places set to auto. Thanks!
Am currently working on a basic inventory system, for the most par its going ok, but have come across one strange . I have a field labled VAT to which I want to return the value 17.50 (NOT 17.50 %) but whenever I key it, it automatically changes to 18.00, have tried changing the format but no joy,
I ve performed a query on a table and now have a set of results which is fine for example £1.56, £1.98, £87.90 (ive set the values in to a currency format), now i want to calculate the total so i have run another query on the last and used 'sum' in the 'total' this calculates the value but rounds it to the nearest integer for example 91!
the thing is i have done this many times before and havent had this problem! i cant understand it, its seriously doing my head in. Ive tried allsorts including the 'round' function which doesnt alter it.
Hi All, I hope someone can help me with this odd little problem!
I have a query with 2 fields, one called 'list size', which contains 4 digit numbers, and another calculated field called 'list size per 100', which has the simple formula '[list size]1000'. The problem is this field rounds the result down, so for example where the list size is 8979, the list size per 100 should be 8.98, but is giving me 8.00! How can I resolve this? I have tried setting the properties to different things but no luck.
I am running a query that returns the minimum, maximum, and average mileage of a list of cars. I have set decimal places to zero in all places I can think of, but of course the average returns with a long decimal output. These figures are then displayed on a report.
How can I truncate the 'average' display so it rounds to show no decimals?
I have tried using Round([YourNumberFieldName],3) in the query but that doesn't seem to work.
The numbers are stored in a table as double numbers. Again the decimal places are set to 0.
My company is a service company, (Commercial Heat Treating), for heat treating metal automotive parts. We do not manufacture any products. We already use 2 separate databases for job control. Ie. Entering a new job. Generating process paperwork with processing instructions. Finally, when the order is completed and ready to be returned to our customer, we generate a shipping ticket for that order.
I am creating an Accounts Receivable database that will retrieve customer, part number, PO number, quantity and other selected information from the other 2 databases. I have linked the shipping ticket tables from each of them to the Accounts Receivable database. The invoicing form has a subform that is used to pull the outside data. I use a combo box to select a shipping ticket number. After the shipping ticket number is entered, the other information is populated to the proper fields. I have created 6 additional fields on the subform. Fields 1, 2 & 4 are for data entering. Fields 3, 5 & 6 will be populated with the results. The fields are in order:
I am trying to total each line on the subform and end up with a grand total for a given record. However, the line totals will not add correctly which in turn gives me an incorrect grand total. The (LineTotal) values are $00.01 low, match exactly or $00.01 higher. Please review this info to see if a simple fix is evident.
The first 2 items perform as they should. All dollar amounts $XXX.0001 and over round to the next whole cent. This is what I want. The grand total code does not add the rounded values.
Private Sub UnitPrice_AfterUpdate() [LineSubTotal] = Me.Multiplier * Me.UnitPrice
End Sub
[LineSubTotal] rounds correctly = Example 941 x $0.145 = $136.45 (exact = 136.445) [LineSubTotal] is set to 2 decimal places.
Private Sub SurchargePercent_AfterUpdate() [SurchargeTotal] = Me.LineSubTotal * Me.SurchargePercent / 100
End Sub [SurchargeTotal] rounds correctly = Example $136.45 x 4% = $5.46 (exact = 5.458) [SurchargeTotal] is set to 2 decimal places.
=Sum(Nz([LineSubTotal])+Nz([SurchargeTotal])) Does not add rounded currency correctly = Example $136.45 + $5.46 = $141.90
The problem is any cent amount that is below .0049 rounds down. I want to force it to round up to the next whole cent.
I have tried codes CLng, CCur, Rnd.
Some more info on my problem. I believe the problem lies within the table. I have formatted the [LineSubTotal], [SurchargeTotal] and [LineTotal] fields to be currency with 2 decimal places. The fields show 2 decimal places until I click on that field. At that point it shows 3 - 4 decimal places. The codes I have written will not take the visible value; they take the true value. How can I force 2 decimal places that will go to the next whole cent? If it is $00.0101, I need to force it to $00.02.
I have a sum in my section footer and again in my page footer that can be off by 1 penny. I have the format set for currency and decimal places to auto. Is there anything that anyone knows of that I'm doing wrong. I just assume that it is rounding.
I have a table that allows for data to be automatically imported into it or manually entered. When data is automatically entered into the table the number field shows the number as a decimal, but when I manually entered data into the column it rounds it up or down to the nearest whole number. I have checked the the decimal place setting on the particular item and it is set to allow for 2 decimal places the format is fixed and the number itself is Long integer
