Unusual rounding error

[Roscoe24]

Have already spent 5 hours on web - no answer. Can you help?

Have a temporary table being fed from VBA
E.g. ((Field A - Field B) / 100) * Field C
Math = (£925,000 - £250001) / 100) * 8 = £53,999.92
Answer presented = £54,000

All table fields at Currency / Currency / 2 decimals
All fields on s/f at Currency / 2 decimals.

To quote the old joke - Yes I have (done a compact & repair &) opened/closed the db!

 

[Ranman256]

what about format?
i dont see any decimals showing for your answer. did you set the format = .00?

 

[Gasman]

I think the key would be what type are your VBA variables?

 

[jdraw]

Please show us the complete code with data types. Or post a copy of the database.

 

[gemma-the-husky]

The result of the calculation is actually 53999.992, not 53999.92

It should still round to 2dps as 53999.99

maybe the calculation (fielda - fieldb) /100 is rounding, before the 8x multiply operation.

I would double check that the types of the 3 fields are currency.
Maybe add some part calculations to see where the error is arising.

Or try everything to 4dps, to see what happens.

 

[Roscoe24]

Thanks to all who have felt my pain and replied. I appreciate your help.

All number fields in table set to Double, 2 dps, #.##
All currency fields in table set to Auto, 2 dps

I've even recreated the form from the main table instead of the temporary one - and done away with the temp completely.

A s/f contains data in currency format (in table & form) of Field [To] and Field [From], by putting in a Break, I find that £250,001.45 copies to the main form as £250,000.
The code used is simply:

Me.SDLT3.value = Me.rfrmSDLTResInc3.Form.Res2SDLTRateTo

I have no Dim variable set - but maybe need some???

Can't send db because it's got a lot of confidential data in it.

Again, thanks to all who have replied so far.

 

[Galaxiom]

If it is calculated in VBA you will need to Dim any of the variables used as Currency.

Also check out the Format and DecimalPlaces properties of the textbox on the main form.

 

[Roscoe24]

They say perseverance doesn't pay! It does - plus the help of complete strangers who make you think ...

Solved! I built up the line of code bit by bit - and rechecked at every stage to find variations from my old fashioned calculator. From this one line of code (which now works thanks to multiple tweaks - techi term!) I shall now adapt all other lines to conform.

Many, many thanks all of you.

 

[Gasman]

And the cause was?

They say perseverance doesn't pay! It does - plus the help of complete strangers who make you think ...

Solved! I built up the line of code bit by bit - and rechecked at every stage to find variations from my old fashioned calculator. From this one line of code (which now works thanks to multiple tweaks - techi term!) I shall now adapt all other lines to conform.

Many, many thanks all of you.