# SolvedRounding (1 Viewer)

#### tmyers

##### Active member
I am having various issues with rounding across my db.
A lot of the numbers I deal with are in currency format. Both the controls and the fields within the tables are set to currency, yet it appears it is still rounding to the .0001 place (roughly) and is resulting in some interesting numbers.

I looked in the Round() function, but that rounds to the nearest whole dollar up and down. Per my boss, he wants to round to the nearest tenth and always up like excels RoundUp function.

So items like \$10.01 go to \$10.10 and \$10.11 go to \$10.20 etc. etc.

Would I just use the Int function? And to get to the tenth, would it be like Int(-10 * [myfield]) / -10?

#### Minty

##### AWF VIP
Currency is fixed at 4 decimal places. It will do 4/5ths rounding beyond that, which should mean you never get a problem variance at the cent level, as it's accurate to 1/1000 of a cent

Rounding up to the nearest 10c is not a standard accountancy rounding, so you would need to roll your own - your function looks about right, but test it on the same numbers you are having issues with..

#### Gasman

##### Enthusiastic Amateur
I am having various issues with rounding across my db.
A lot of the numbers I deal with are in currency format. Both the controls and the fields within the tables are set to currency, yet it appears it is still rounding to the .0001 place (roughly) and is resulting in some interesting numbers.

I looked in the Round() function, but that rounds to the nearest whole dollar up and down. Per my boss, he wants to round to the nearest tenth and always up like excels RoundUp function.

So items like \$10.01 go to \$10.10 and \$10.11 go to \$10.20 etc. etc.

Would I just use the Int function? And to get to the tenth, would it be like Int(-10 * [myfield]) / -10?
Where did you get that idea?

Code:
``````? round(10.1234,2)
10.12
? round(10.1256,2)
10.13
? round(10.0111,2)
10.01``````

#### tmyers

##### Active member
Currency is fixed at 4 decimal places. It will do 4/5ths rounding beyond that, which should mean you never get a problem variance at the cent level, as it's accurate to 1/1000 of a cent

Rounding up to the nearest 10c is not a standard accountancy rounding, so you would need to roll your own - your function looks about right, but test it on the same numbers you are having issues with..
I tried adding that to my expressions and am still getting weird results.
Here is a break down:

Unit Price: Int(-10*([Price]+IIf(IsNull([Freight]),0,[Freight])+(IIf(IsNull([LampPrice]),0,[LampPrice])*IIf(IsNull([NumberOfLamps]),0,[NumberOfLamps])))/(1-[GP]))/-10

Then I have:

Extended: Int(-10*([Quantity]*(([Price]+IIf(IsNull([Freight]),0,[Freight])+(IIf(IsNull([LampPrice]),0,[LampPrice])*IIf(IsNull([NumberOfLamps]),0,[NumberOfLamps])))/(1-[gp]))))/-10

The very first line in the report is (79) items @ \$216.00/e (which is correct). The extended for this item somehow came out to \$17,056.90. The amount SHOULD have been \$17,064.00. How in the world did it get to that other number? Not only is it close to being \$10 off, it has .90 when it was multiplied by a whole amount.

#### Minty

##### AWF VIP
I tried adding that to my expressions and am still getting weird results.
Here is a break down:

Unit Price: Int(-10*([Price]+IIf(IsNull([Freight]),0,[Freight])+(IIf(IsNull([LampPrice]),0,[LampPrice])*IIf(IsNull([NumberOfLamps]),0,[NumberOfLamps])))/(1-[GP]))/-10

Then I have:

Extended: Int(-10*([Quantity]*(([Price]+IIf(IsNull([Freight]),0,[Freight])+(IIf(IsNull([LampPrice]),0,[LampPrice])*IIf(IsNull([NumberOfLamps]),0,[NumberOfLamps])))/(1-[gp]))))/-10

The very first line in the report is (79) items @ \$216.00/e (which is correct). The extended for this item somehow came out to \$17,056.90. The amount SHOULD have been \$17,064.00. How in the world did it get to that other number? Not only is it close to being \$10 off, it has .90 when it was multiplied by a whole amount.
I'm not going to try and decipher that second calculation!
Do yourself a favour and create a query that does all the is Null value replacements first.

Then make another query to do all the maths and rounding work from that.

I suspect all will become a lot clearer then.

#### tmyers

##### Active member
I'm not going to try and decipher that second calculation!
Do yourself a favour and create a query that does all the is Null value replacements first.

Then make another query to do all the maths and rounding work from that.

I suspect all will become a lot clearer then.
Never thought of doing that.

Those formulas are a royal pain to look at so I can't blame you.

#### tmyers

##### Active member
Got it. Had to do the Int function against that mess and then multiply by the quantity.

I am still going to do your suggestion and break that up. That expression is a royal pain to read.

#### Minty

##### AWF VIP
Often, even if not strictly necessary, breaking things down into more manageable pieces is the best way.
Good luck with the rest of your project.

#### tmyers

##### Active member
Often, even if not strictly necessary, breaking things down into more manageable pieces is the best way.
Good luck with the rest of your project.
Agreed. It is a simple function from a technical standpoint. It is just setting various nulls to 0 so that the formula can correctly add things together. Downside is it is doing it for 3-4 fields so the expression is quite large.

Replies
24
Views
554
Replies
7
Views
332
Replies
5
Views
597
Replies
3
Views
484
Replies
6
Views
643