VAT Rounding

[easykey]

I have this module to help physically round the currency
figures to pence....


Option Compare Database
Option Explicit

Const Factor = 100

Function RoundVAT(X)

RoundVAT = Int(X * Factor + 0.5) / Factor
End Function

The problem is that it seems that accountancy rules
suggest that .005 needs to round UP

So at the moment £165 net produces £28.87 (where VAT is
17.5%) In other words the true figure 28.875 needs to be
returned as £28.88 ...any suggestions?

 

[Nikos Yannacopoulos]

Isn't that kind of an overkill? Use Access's built-in function Round(Number,
Decimals) which will do exactly what you want.

Round(28.874, 2) = 28.87
Round(28.875, 2) = 28.88

HTH,
Nikos

I have this module to help physically round the currency
figures to pence....


Option Compare Database
Option Explicit

Const Factor = 100

Function RoundVAT(X)

RoundVAT = Int(X * Factor + 0.5) / Factor
End Function

The problem is that it seems that accountancy rules
suggest that .005 needs to round UP

So at the moment £165 net produces £28.87 (where VAT is
17.5%) In other words the true figure 28.875 needs to be
returned as £28.88 ...any suggestions?

 

[easykey]

Aha, If only things were that simple!

Unfortunately it seems that I have to use a module that
physically converts the actual number to 28.88 (Not just
DISPLAYING it) This is because it creates problems when I
run a query that Sums a large amount of figures for say a
whole year.

 

[Nikos Yannacopoulos]

But that's what Round does! It rwturns a rounded value, it doesn't just show
it formatted.

Nikos

Aha, If only things were that simple!

Unfortunately it seems that I have to use a module that
physically converts the actual number to 28.88 (Not just
DISPLAYING it) This is because it creates problems when I
run a query that Sums a large amount of figures for say a
whole year.

 

[easykey]

So try this forumula

Round(([ItemCost]*[VATPerc]),2)

If ItemCost is 165 and [VATPerc] is 17.5% the formula
above returns 28.87

If I Round using 3 instead of 2 it returns 28.875

So How do I get it to Round Up the .005 ?

 

[easykey]

I think I have solved it!

The module needs an extra 0!

Option Compare Database
Option Explicit

Const Factor = 1000

Function RoundVAT(X)

RoundVAT = Int(X * Factor + 0.5) / Factor
End Function

Then in the Query I have
CCur(RoundVAT([ItemCost]*[VATPerc]))

Which seems to round it correctly and display it as a
currency

-----Original Message-----
So try this forumula

Round(([ItemCost]*[VATPerc]),2)

If ItemCost is 165 and [VATPerc] is 17.5% the formula
above returns 28.87

If I Round using 3 instead of 2 it returns 28.875

So How do I get it to Round Up the .005 ?

-----Original Message-----
But that's what Round does! It rwturns a rounded value, it doesn't just show
it formatted.

Nikos

"easykey" <[email protected]> wrote

in
message

Aha, If only things were that simple!

Unfortunately it seems that I have to use a module that
physically converts the actual number to 28.88 (Not just
DISPLAYING it) This is because it creates problems when I
run a query that Sums a large amount of figures for say a
whole year. in
message


.

.

 

[Nikos Yannacopoulos]

I think you are a victim of Access calculation accuracy. Try the following
in the immediate window:
?round(165*.175,2)
it reruns 28.87

then:
?round(28.875,2)
it returns 28.88

Then try:
?format(165*.175-28.875,"0.000000000000000000000000000000")
it returns -0.000000000000001831867990631510 !!!
which means that Access calculates 165*.175 as something short of 28.875,
only you need 15 decimals before you can see it!

Nikos

I think I have solved it!

The module needs an extra 0!

Option Compare Database
Option Explicit

Const Factor = 1000

Function RoundVAT(X)

RoundVAT = Int(X * Factor + 0.5) / Factor
End Function

Then in the Query I have
CCur(RoundVAT([ItemCost]*[VATPerc]))

Which seems to round it correctly and display it as a
currency

-----Original Message-----
So try this forumula

Round(([ItemCost]*[VATPerc]),2)

If ItemCost is 165 and [VATPerc] is 17.5% the formula
above returns 28.87

If I Round using 3 instead of 2 it returns 28.875

So How do I get it to Round Up the .005 ?