Solved Rounding (1 Viewer)

tmyers

Well-known member
Local time
Today, 14:54
Joined
Sep 8, 2020
Messages
1,090
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.

I am reading:

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

Minty

AWF VIP
Local time
Today, 18:54
Joined
Jul 26, 2013
Messages
10,355
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
Local time
Today, 18:54
Joined
Sep 21, 2011
Messages
14,046
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.

I am reading:

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

Well-known member
Local time
Today, 14:54
Joined
Sep 8, 2020
Messages
1,090
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
Local time
Today, 18:54
Joined
Jul 26, 2013
Messages
10,355
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

Well-known member
Local time
Today, 14:54
Joined
Sep 8, 2020
Messages
1,090
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

Well-known member
Local time
Today, 14:54
Joined
Sep 8, 2020
Messages
1,090
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
Local time
Today, 18:54
Joined
Jul 26, 2013
Messages
10,355
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

Well-known member
Local time
Today, 14:54
Joined
Sep 8, 2020
Messages
1,090
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.
 

Users who are viewing this thread

Top Bottom