Rounding Function

[tinymind]

Simple One .. ..

How do I round a number to 2 decimal places in a query within Access.

Excel has Rnd() as a formula but Access doesn't allow it .. .

Any suggestions?

 

[AlexMidd]

If the field in the query is from a table you can set the format of the number and the number of decimal places in the table design mode. If the field is an expression, you could use the format function to force it to display only the number of decimal places you need. Alex Middleton

 

[Lavey]

How can i get access to stop rounding my decimal places to 0, i have tried setting the format on the table (to currency - 2 d.places) and the control on the form (wich is an expression) to currency!
It still rounds off to zero !!

 

[tinymind]

Access for some reason does the following:

Query Expression- Current: [DebtValue]-[PaidValue] will give me varying decimal places in the results.

The Tables have been set to Double with 2 decimal places and I get that result with null default value.

Is there is a solution to this as it would save me a lot of time fiddling with results.

....

 

[AlexMidd]

I have not had time to test this but try:

Current: Format([DebtValue]-[PaidValue],"#,##0.00&quot

You can change the "#,##0.00" part depending on your specific needs as you may need millions or some other possible amount to be formatted. The above will work up to thousands (e.g. 1,234.56).

Alex Middleton

 

[AlexMidd]

Reply to Lavey:

What Field Size is set for this number? I have tried using a Currency format with an integer and it always rounds the decimal part to zero. Ensure that the field size is a floating point variable, i.e. Single or Double. Alex Middleton

 

[Lavey]

Alex, I have set to double size and this worked! Thanks,
(declaring variables as long doesn't help either !!!)
LOL

 

[cschnable]

If you are doing monetary calculations be careful with the Round function. You may run into the problem of extra pennies showing up where you don't want them.

the Round function only hides the extra decimal places and those extra decimal places can show up in your final calculations as being a cent off. (Bookkeepers sometimes spend hours at $30.00 and hour trying to find the lost penny).

You may want to use the Fixed function instead of the Round function. The Fixed function REALLY sets the decimals to two places and you won't have those extra pennies showing up later to haunt you or the bookkeeper.

 

[tinymind]

Thank You all - Problem sorted!

 

[Aivars]

cschnable!

You aren't right! Function Round() really round values!

abc=Round(12.9873423)
?abc
13
abc=Round(12.9873423,2)
?abc
12.99
?abc*2
25.98

Aivars

 

[cschnable]

Aivars -

I could be wrong, but I had quite a problem recently in an accounting package which I developed, and it was with the Round function.

I think that you will find that if you perform further calculations with a previously rounded number, the calculation will be performed on the extended decimal places, not on the rounded (visable) decimal places.

If you go to the field of the rounded figure and place your cursor over the field you will notice that the extended decimals show up, if you do the same with a Fixed figure only the two decimal places will show.

I cleared up the problem by using the Fixed function and all the extra decimal problems were cleared up.

I am going to research this further to see if I am nuts or what <grin>.

 

[cschnable]

OK - SO I&quot;M NUTS

I APOLOLOGIZE TO YOU AIVARS !!!!!!!!

Now I have to go back into my program and find out why the decimals were showing before I changed the fields to text by using the fixed function.

I APOLOLOGIZE TO YOU AIVARS !!!!!!!!