Rounding Numbers
Dec 8, 2006
Hi
Does anyone know how to round a number in the query designer.
Thanks
Dharmesh
This is what i have typed into my field in the query designer. what am i doing wrong.
Base price: Round([base price],1)
View Replies
ADVERTISEMENT
Jan 8, 2008
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
View 5 Replies
View Related
Feb 17, 2006
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?
thanks
View 3 Replies
View Related
Feb 9, 2007
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)
Thanks for your help
View 1 Replies
View Related
Mar 13, 2015
I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...
View 10 Replies
View Related
Oct 20, 2014
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
[Code] ....
View 5 Replies
View Related
Aug 12, 2014
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.
View 14 Replies
View Related
Jan 10, 2007
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?
View 1 Replies
View Related
Aug 24, 2014
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.
Example
1 1
2 2
3 3
2 1
2 2
2 3
View 5 Replies
View Related
Jan 8, 2007
In My Table I Want To Type A Number (4.2) But I Cant Get Access Not To Round That To (4.0) Any Suggestions
View 1 Replies
View Related
Feb 12, 2008
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?
View 1 Replies
View Related
Feb 9, 2005
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.
Thanks
Chris
View 3 Replies
View Related
Jan 8, 2007
I Want To Type 4.2 And Access Is Rounding It To 4.0...ive Tried Changing The Format To General Or Fixed But I Cant Get Access Not To Round
View 3 Replies
View Related
Jul 23, 2005
Have the need to Round up to the next whole. If I have 1.2, I would like it to Round to 2. Is this possible? :
View 6 Replies
View Related
Dec 15, 2005
I want to round up to whole number based on U. the else needs to round up to the nearest cent. i.e. 1.64 rounds up to 1.65.
The first part works but I can figure out how round up the cents in the else part.
IIf([Item type]="U",+1*Int(+1*[Gross]*1.03),([Gross]*1.03)
Any help would be greatly appreciated.
Michael
View 10 Replies
View Related
Oct 13, 2006
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?
Thanks
:confused:
Judo
View 5 Replies
View Related
Jul 29, 2005
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.
Any ideas?
View 4 Replies
View Related
Sep 18, 2007
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
Any help would be so much appreciated!
View 11 Replies
View Related
Sep 16, 2005
I have got a formula in a Query as follows:
Suggested Qty: [StockQty]/[Numerator]
How do I round this down to the nearest 1?
(i.e 7.2 = 7 or 9.9 = 9)
Any help would be greatly received.
Regards
Carly
View 3 Replies
View Related
Oct 27, 2005
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?
Thanks.
Dawn
View 8 Replies
View Related
Nov 22, 2005
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.
View 2 Replies
View Related
Mar 5, 2006
Hi, I am quite new to access, and have the following problem:-
I have a value that is returned in a report of say 17.93, i would like to round this number down to 17, similarly 9.13 should read 0
Can someone help please? Many thanks in advance
:)
View 5 Replies
View Related
Mar 19, 2007
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.
ie.
Item QTY
Pipe 534.08
Pipe 405.5
Pipe 28.83
Pipe 1381
Item QTY
Pipe 540
Pipe 420
Pipe 40
Pipe 1400
Another problem I'm not sure how to fix is, some of the qty totals are two decimals and others are nine. Maybe not an issue but thought it might be.
So if anyone is compelled to help a greenie out it would be much appreciated.
Thanks
View 6 Replies
View Related
Jan 19, 2005
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.
Hope this makes some sense. Thanks!
View 1 Replies
View Related
Jan 20, 2005
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!
View 1 Replies
View Related
Jul 29, 2005
Hello,
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,
Any ideas?
Cheers
View 2 Replies
View Related