Rounding in currency fields

[Malcy]

Hi
I have a slight problem with my job cost database where I have been caught out with two sums summing inaccurately to a third figure. I think it is to do with rounding.
Before anyone shouts I do store calculated totals in a table since I have to be able to recreate an invoice on demand (for clients, accountant, records, tax etc) and so having not just a snapshot file of the individual invoices but also the calculated data seems sensible. I know it does not tie in with normalisation.

I have a query which draws relevant time sheet entries and produces a by line invoice value (i.e. time * rate). This can easily lead to a value of £14.783p which rounds to £14.78
I then have a similar process to list expense costs - which are always straight currency figures say £10.00.
My invoice then totals these two figures to give a nett sum, in this simple case £24.78.
I then need to add 17.5% VAT which is calculated off the nett sum and gives, in this case £4.3365p which rounds to £4.34
My invoice is thus £29.12 which is fine.

However, if there are a lot of time sheet entries then the roundings can get complex and I end up with the rounded nett sum, and the rounded VAT sum coming to less than the total invoice sum. It was only out by 1p but that does not invoke confidence in an accounting system.

Can anyone point me in a direction that would help me to resolve this? I am happy to drop my calculated table if I can find a more robust way of meeting all my requirements.
Any help would be much appreciated.

 

[Pat Hartman]

You need to use the "rounded" value in subsequent calculations. You have to use £14.78p rather than £14.783p. If you use £14.783p, the rounding errors will accumulate until finally it looks like your numbers don't add up.

 

[pono1]

Malcy,

I may be missing something because the solution seems to be to simply store the total invoice value once the invoice is "finalized." (Be on notice too that the Round function rounds up odd numbers that are exactly 0.5 and rounds down even numbers that are exactly 0.5 -- the accountants will eventually catch that, if they already haven't.)

Regards,
Tim

 

[Malcy]

OK
Thanks Pat and Pono
From reading through one change I have made is to move the decimal on the currency format on the fields in my tblInvoices from auto to 2. I will see if that makes a difference.
Also need to learn to quickly double check the invoices before I send them out rather than just assuming they have come out correctly! Ah blind faith!
Not sure what to do with Pono's comment on rounding for 0.5 differing between odd and even numbers. My maths would suggest to always round up.
Thanks again for the thoughts
Best wishes

 

[Pat Hartman]

The idea behind the round up on odd/round down on even (or whichever way it does it) is to average the error that rounding induces. 1-4 always round down. 6-9 always round up and 5 rounds up or down depending on whether the up value is odd or even. With 5 always rounding up, the results are slightly skewed since 4 possibilities round down and 5 round up. This method removes that skew since 5 rounds up half the time and down half the time.

Setting the decimal places to .2 only affects what is displayed, not what is stored. Up to 4 decimal places will still be stored and they will skew your sums if you add enough numbers. Round the calculation to 2 decimal places and use that rounded number in further calculations. Your totals will always be exact.

Of course if you are dealing with millions of transactions and you want to make yourself some money, create a new account. Then truncate to 2 digits and give your new account the truncated fractions. When you accumulate enough money, you can head for the South Pacific.

 

[Malcy]

Thanks Pat
When you say round the calculation to two figures, can follow the logic precisely, but how would I do this?
Would I use the property in the query?
As far as millions of transactions - I wish!!!
Best wishes

 

[Rich]

The way Access handles currency calculations is bloody frustrating to say the least

I found this formula here and have to use it throughout the db to ensure accuracy

Public Function Round2CB(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Round2CB = Value

End Function


here's just one example of its use in a query

SELECT DISTINCT IIf(IsNull([fldPymntDate]),Round2CB([Amount]),IIf([fldDtePrinted]>[tblInvRcpts].[fldPymntDate] Or IsNull([fldDtePrinted]),Round2CB([fldPymnt]),Round2CB([Amount]-Nz([TotPd])))) AS Amnt, IIf([fldDtePrinted]>[tblInvRcpts].[fldPymntDate] Or IsNull([fldDtePrinted]),[fldPymntDate],[fldDtePrinted]) AS Issued, Jobs.fldPMID, Jobs.AddressID, Jobs.JobID, Calc.Expr4
FROM ((Jobs INNER JOIN Calc ON Jobs.JobID = Calc.JobID) LEFT JOIN qryInvStagePaym ON Jobs.JobID = qryInvStagePaym.JobID) LEFT JOIN tblInvRcpts ON Jobs.JobID = tblInvRcpts.JobID
WHERE ((Not (IIf([fldDtePrinted]>[tblInvRcpts].[fldPymntDate] Or IsNull([fldDtePrinted]),[fldPymntDate],[fldDtePrinted])) Is Null))
ORDER BY IIf([fldDtePrinted]>[tblInvRcpts].[fldPymntDate] Or IsNull([fldDtePrinted]),[fldPymntDate],[fldDtePrinted]);

 

[Alan R]

Round2CB Function - Undefined function in expression

Hi,

I have aproblem with rounding currency values and a search of this Forum brought up this thread and in particular the Round2CB Public Function above.

I have a problem in that when i attempt to use this function within a query i get a 'Undefined Function in Expression' - can anybody shed some light on why this may be happening please. I have checked for typos within the function.

Regards

Alan

 

[Rich]

Have you created the function in the modules section and saved it with a different name?

 

[Alan R]

Rich,

Have you created the function in the modules section and saved it with a different name?

Yes saved it in the modules section and saved as Round2CB, just a thought but would using numbers in the name affect it?

Alan

 

[Alan R]

Rich,

Resaved it as RoundCurrency and called that function and appears to be working.

I take it then that module names cannot contain numbers? Although default names are Module1 etc!!

Anyway, as i said it now appears to be working - many thanks

Alan

 

[Alan R]

Undefined Function Error

As you will see form the above threads i had a problem getting a Public Function to operate.

I thought i had cured this but when i now open the database it comes up with the Undefined Function 'RoundCurrency' in expression. i have checked for missing references but nothing - can anybody offer some advise please.

SSoooooo frustrating - it was working fine earlier.

Alan

 

[Rich]

Have you compilled and saved all Modules?

 

[Pat Hartman]

I take it then that module names cannot contain numbers? Although default names are Module1 etc!!

No, that's not a problem. However, the module must NOT be the same name as any procedure. So if you called the function "abc" and the module "abc", the function would not work.

 

[Alan R]

Pat,

I got it working in the end by doing just that - different name for module and function.

Thanks for taking the time to respond - much appreciated.

Alan

 

[gemma-the-husky]

i have that problem, and it is hard to resolve satisfactorily

if you work out the VAT on every line of an invoice, and round each line to 2dp's, the total VAT may well not exactly represent 17.5% of the total invoice.

the other way is to accumulate the invoice line totals of each VAT category (ie standard rated, zerorated) and then apply the VAT calcs to the accumulated totals, which avoids the rounding differences.

 

[Rich]

All of the suppliers that I use add vat to every line item, I know of none that add the vat to the actual subtotal.
More money for the government!