[Access 2003] Cannot seem to add/compare properly?

[Ecniv]

I have some data in a table...

Net : 37673.88
VAT : 5701.42
Gross : 43375.3

Net + Vat = Gross.... at least it looks like it for all intents and purposes... even recoded into recordset to ensure the values...

Access is saying that the two numbers do not match.


What???!

What is the best way to fix this?

[anhn]

That is the nature of floating numbers (Double or Single)!
You don't have that problem if 3 fields have Currency data type.

Sub X1()
    Dim net As Double
    Dim VAT As Double
    Dim Gross As Double

    net = 37673.88
    VAT = 5701.42
    Gross = 43375.3
    
    Debug.Print Gross, net + VAT, (Gross = net + VAT), Gross - (net + VAT)
    'return:  43375.3       43375.3      False          5.45696821063757E-12
End Sub

Sub X2()
    Dim net As Currency
    Dim VAT As Currency
    Dim Gross As Currency

    net = 37673.88
    VAT = 5701.42
    Gross = 43375.3
    
    Debug.Print Gross, net + VAT, (Gross = net + VAT), Gross - (net + VAT)
    'return:  43375.3       43375.3      True           0
End Sub

You should not store a calculated field in the table.
Gross always can be calculated from net and VAT.

[Ecniv]

You should not store a calculated field in the table.
Gross always can be calculated from net and VAT.

Yeah I know that, but this is to hold what the other company have on an invoice.. ergo need to hold it. And in this case they mistyped one gross and were a grand under.... doh!

Weird over the double thing, cause I took them in as text, converted to double, formatted to two decimals. The still didn't match.

In the end I did all the conversions above, then took the total from the gross, using abs function and if this was <1 I ignored it. Hmm might need to amend that to 0.01

Still does teh job.


Thanks

[anhn]

Why do you have to convert them to Double but not Currency? That is the same process.

If you cannot convert them to Currency then that is a good idea to check:
If Abs(Gross - (net + VAT)) < 0.01 Then

But this may be better:
If Round(Gross - (net + VAT), 2) = 0 Then

[Ecniv]

Hiya,

I dont see why it should make a difference - if I put em in double they should still be the same and not change, otherwise converting to double, then debug.printing would give different values. It doesn't.

Just because I dont use currency. A bit like I like to use on error resume next (ok so I have cut down after some errors snuck through and most of the data was ok - enough to fool. So only use it in dire circumstances.

doesn't make much difference... :/