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
when enetering data into a field on a form it automatically rounds it up the number up to the nearest whole number. this is really frustrating, does anyone know how i can solve this problem?
I am looking to round numbers to specific values 2, 4 and 6.
I was thinking of a code in a query something like:
if <3 but >0 then 2 if <5 but >3 then 4 if >5 then 6
can anybody convert this into queery code, not SQL tho because I'm rubbish at that. I just want to type it into the criteria on my field box on the query.
I also just tried to do it with:
IIf(>0 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0))))
but this only seems to return results of 4 and doesn't show all entries, like entries with no value entered. I also think i'm missing out the numbers 3 and 5 because it's less than and greater than but not the number itself: if 5 or < or > (to include the 5)
thanks
Ok I've now got IIf(<1,0,IIf(>1 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0)))))
which I think is doing roughly what I need but i have stumbled on a new problem.
These results are based on the average result of another query but i need to add a section before the results are averaged that says
if(=0 or no entry then all results are 0 for that unit else avg but iif(<1 etc. (the original statement above)
I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx
I am now using it as a "Integer" but that is not working very well..
Code: Dim iRet As Integer Dim strPrompt As String Dim strTitle As String Dim max_nog_leveren As Integer Dim strQuery As String Dim nettohoeveelheid_toevoegen As String
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
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 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 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.
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,