Calculating VAT

[Guest]

Hi

I have an invoice report that calculates the VAT element this works fine apart from when the pence element is computed to be half a penny eg 0.655 when the system will then round down to 0.65 instead of rounding up to 0.66.The code I am using in the control source text box is as follows: =(RoundCC([total2]*0.175)) with total2 being the vatable item. I would be very grateful if anyone could advise a solution

Regard
Norman

 

[Wayne Morgan]

To help, I would need to see what the user defined function RoundCC is
doing. Can you post the code for it?

--
Wayne Morgan
Microsoft Access MVP

Hi

I have an invoice report that calculates the VAT element this works fine

apart from when the pence element is computed to be half a penny eg 0.655
when the system will then round down to 0.65 instead of rounding up to
0.66.The code I am using in the control source text box is as follows:
=(RoundCC([total2]*0.175)) with total2 being the vatable item. I would be
very grateful if anyone could advise a solution.

 

[Guest]

Thanks Wayne, the user code is as follows:

Function RoundCC(x)
On Error GoTo Err
RoundCC = Int(x * Factor + 0.5) / Factor
Exit Function
Err:
MsgBox "Error with Rounding"
Exit Function
End Function

 

[Wayne Morgan]

Right off-hand it looks correct. I tried 3.74 and 3.75 for Total2 and it
gave 0.65 and 0.66 respectively, as expected. I am going to guess that you
are getting caught in floating point errors. There are some ways around this
if that is the case.

Function RoundCC(x)

Try
Function RoundCC(x As Currency) As Currency

You don't specify what Factor is, I assume it to be an integer and in this
case, rounding to two decimals, should be 100.

Also, let's try to force some greater floating point accuracy in the
function.

Function RoundCC(x As Currency) As Currency
On Error GoTo ErrHandler
Dim Factor As Integer, Rounded As Currency
Factor = 100
Rounded = Int(x * Factor + 0.5) / Factor
RoundCC = Rounded
Exit Function
ErrHandler:
MsgBox "Error with Rounding"
End Function

Theoretically, it shouldn't be necessary to do this, but it does help at
times. Also, Err is a reserved word, I would recommend not using it as a
label, just to be safe.

If this isn't sufficient, you may have better luck passing the numbers to
the function as parameters and doing the entire calculation in the function,
one step at a time.

 

[Guest]

Thanks Wayne for your help I have tried the code you suggest but get the same result. The problem only occurrs when the vat calculation is half a pence For example take a net invoice figure of £33495.00. The precise VAT calculation is £5861.6250 this should be rounded up to £5861.63 but it is rounded down to £5861.62. If the VAT calculation had been £5861.6249 or £5861.6251 the calulation would be OK ie rounded down and up respectively. If you should have any other ideas it would be greatly appreciated

Regard

Norman

 

[Wayne Morgan]

Did you try the option of passing all of the numbers to the function and
letting the function do the math?

--
Wayne Morgan
Microsoft Access MVP

Thanks Wayne for your help I have tried the code you suggest but get the

same result. The problem only occurrs when the vat calculation is half a
pence For example take a net invoice figure of £33495.00. The precise VAT
calculation is £5861.6250 this should be rounded up to £5861.63 but it is
rounded down to £5861.62. If the VAT calculation had been £5861.6249 or
£5861.6251 the calulation would be OK ie rounded down and up respectively.
If you should have any other ideas it would be greatly appreciated.

 

[Wayne Morgan]

Is there a chance that the number that appears to be at .5 is not really but
instead is showing "rounded" itself?

 

[Guest]

No. The number in the example is 5861.6250. This is the VAT calculation on the net figure of 33494.00. If you do the calulation manually this is what you get.

 

[Wayne Morgan]

Can you zip the file and send a copy to (e-mail address removed)? Let me know
where to look in the file and which record is causing the problem.

--
Wayne Morgan
Microsoft Access MVP

No. The number in the example is 5861.6250. This is the VAT calculation on

the net figure of 33494.00. If you do the calulation manually this is what
you get.