Rounding Problems Calculating Sales Tax

[loonette]

I am calculating Sales Tax in a query. I need the sales tax to round up, but I've tried everything I've found on the internet and in this forum and I can't get anything to work. Here is what I am trying to do:

tax: (Switch([taxable]="y",([finalprice]*[taxrate]),[taxable]="n",([finalprice]*0)))

The Tax rate is set at 5.5% and say for instance the item sells for $1.00, the tax calculates to .055, which rounds up to $.06 in the form, but when you click in the box it shows up as .055 and makes the total 1.055. I need it to calculate as .06 and make the total 1.06. I tried to use this formula:

Expr1: CCur(Nz(Round([finalprice]*[TaxRate],2),0))

Just to see if I could get it to round and it worked, but it rounded down to $.05 instead of rounding up to $.06.

Any help would be greatly appreciated.

[TheSmileyCoder]

@loonette
Not really sure what to say here. I tested it in Access and it rounds 0.055 up to 0.06. Why you would be getting a different result is beyond me.

[loonette]

Which formula did you try? Was it the first or second? Because the first rounds up to .06, the second rounds down to point .055. Below you can see what is going on on my form, it is calculating .605 and giving me a remainder of .01- when calculating the balance. Thanks!

[TheSmileyCoder]

Well I tested it by writing Round(0.055,2) and got 0.06 then added Ccur(Round(0.55,2) and still got 0.06

[NeoPa]

Like Smiley, I tried it out and consistently got 1.06. I had to amend your figures of course. The TaxRate must be 105.5% or 1.055 for this to work rather than 5.5%.

[loonette]

Okay, here is the formula I have in my query right now:

tax: CCur(Round((Switch([taxable]="y",([finalprice]*[taxrate]),[taxable]="n",([finalprice]*0))),2))

The result that I get when [finalprice]=$1.00 and [taxrate]=.055 is $.055.

What am I doing wrong? I need the tax to be a whole number and for it to round up.

BTW, How do you do the tag thing? Thx!

[TheSmileyCoder]

I have tested what I can from here. If you want me to look further into this, you will need to attach the DB to the thread.

[NeoPa]

Tags (generally) are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [code] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page). You must select the code in your post, and hit the button on the tool bar which looks like a hash (#). This will automatically format the post such that the [code] tags surround the code you're posting. This will display the code in a Code Box, quite differently from the standard formatting of a post.

[NeoPa]

Your formula has an unnecessary set of parentheses () which seems to have confused you. The ,2) parameter for the Round() function is not matching in the correct set. Try :

tax: CCur(Round(Switch([taxable]="y",([finalprice]*[taxrate]),[taxable]="n",([finalprice]*0)),2))

[loonette]

Neopa,

I made the change to formula that you suggested, but it is still rounding down to .05, instead of rounding up to .06.

This is very frustrating. I guess I will go back to the original formula it was at least rounding up to .06.

Thanks for you help.

[NeoPa]

This rounding problem is so strange. In the normal course of events this simply doesn't happen. I'm coming to the conclusion that something else that we don't know about is affecting the result, or maybe even just the appearance of the result. I cannot guess what this may be for now, so let me ask you to run a test for me :

First you need to go to the Immediate Pane. To get there from the main Access window simply press Alt-F11 to open/switch to the Integrated Development Environment (or VBA Editor), then press Ctrl-G to open/switch to the Immediate Pane.

When there paste in the following code and then copy & paste the result (including the code too please) back into a post in here for me to review :

?Round(1.055,2),Round(-1.055,2)

[Stewart Ross]

Hi all. This is a classic example of small errors in the representation of floating-point numbers causing rounding problems. Loonette does not say what the data type of the field storing the tax rate is - double would be better than single but even so this will not affect the basic problem, which is inexact representation of decimal values in a floating-point form.

I am assuming that the data type used for the tax rate is single here, as it demonstrates the problem very clearly. I would not use single-precision values in any circumstances, as seven significant figures is just too low for most purposes, but doubles are not immune either (even with 15 significant figures).

Setting the tax rates to values on the rounding threshold and calculating a resultant sales price shows up the representation problem:

Final Price  Tax Rate  Non-Rounded        Rounded
£1.00        1.015     1.01499998569489   1.01
£1.00        1.025     1.02499997615814   1.02
£1.00        1.035     1.0349999666214    1.03
£1.00        1.045     1.04499995708466   1.04
£1.00        1.055     1.05499994754791   1.05
£1.00        1.065     1.06500005722046   1.07
£1.00        1.075     1.07500004768372   1.08
£1.00        1.085     1.08500003814697   1.09
£1.00        1.095     1.09500002861023   1.1

Note that the calculation of the non-rounded sales price is lower than expected for values of 1.055 and under, but higher than expected for those above. The rounding is working correctly on what it is being fed.

Changing the tax rate data type to Double resolves this problem, at least for this range of values. A more general solution to it is to add to the value being rounded a small amount which will ensure it rounds correctly. Normally such a value is around half of the least-significant figure in the base calculation. In this case, adding 0.00005 to the figure being rounded as follows will also give correct results:

Result = round(FinalPrice * TaxRate + 0.0005, 2)

Conclusion: use the highest-precision data type you can, but even so recognise that representing decimal values in floating-point form is inexact, and the consequences have to be dealt with systematically when encountered. Use rigorous testing of a range of values to ensure that there are no adverse consquences from whatever solution is adopted.

Stewart

[TheSmileyCoder]

Could you not solve it by first rounding by the max precision?

Round(Round(FinalPrice*TaxRate,7),2)

[NeoPa]

I don't believe there is a solution as such. We are dealing with an arithmetic approximation which means that recognising an exact figure (anything.??5) is not always possible.

Actually, an actual solution could be managed by using different variable types, but would be a bit messy. Smiley's proposed solution is probably the tidier, as it doesn't assume the result is low, but either would be practically workable.

As Stewart says, a decimal variable would handle the situation, or another more kludgy way would be an integer (or Long) variable multiplied, then rounded, then divided to handle the decimal places.

Anyway, it was a good spot by Stewart :)

[Stewart Ross]

To answer Smiley's point, rounding the intermediate stage to seven places will not in general help, as the inherent approximation involved is causing errors from the fourth place onwards - any minor rounding at, say digit 7 or 6 will have no effect on the erroneous value of digit 4 unless it pushes the ...999 parts to round up. This will be the case for the thresholds shown, but for general rounding errors it is not going to give predictable results.

Further, if single-precision is used to store the result then rounding to seven places can have no effect on the value, which is already at maximum precision.

-Stewart

[ADezii]

@NeoPa
This could be one of your Kludgies! (LOL)

Public Function MyRound(varValue As Variant, intDecimalPlaces As Integer)
  MyRound = Int((varValue * intDecimalPlaces) + 0.5) / intDecimalPlaces
End Function

'

'Round to 2 Decimal Places
Debug.Print MyRound(1.055,100) produces ==> 1.06

[loonette]

Thanks a million guys, I used Smiley's formula in post 13 and it worked! I didn't realize I was going to start such a debate, but I really appreciate all of your effort to help me fix this problem. Take care!

[TheSmileyCoder]

I have given this some more though, and realise that the suggestion I gave will stop working for larger numbers. I therefore amend it to:
Round(Round(FinalPrice*TaxRate),3),2)

Now it is only basing the rounding in the signaficant figures (unless you exceed 1 million in cost, in which case I doubt anyone will give a flying rats arse about whether the rounding of the last cent was off :)

I can't immediatly think of any place where this might fail. Finally Stewart, im impressed you caught this one :)

[Stewart Ross]

(Blush) Thanks Smiley!

I'm glad that Loonette's problem is now resolved, and with the simple double rounding you've provided rather elegantly too.

-S