4 * 2.4 = 190000038146973 ( !!! ? )

[markjkubicki]

this is BASIC ACCESS 101 (I feel stupid even asking); but, i am.
maybe this issue has always been and i never noticed (seriously ?!)
but i'm noticing it now



2 fields:
QTY -> number, long integer, decimal = auto,
INPUT_LOAD -> number, single, decimal = auto

...i have no idea why one is a 'long integer' and the other 'single' (could be a legacy thing when M$oft changed their field types a few years back?)
both will store a number less than 100,000 and both could have up to 2 decimal places (....maybe not INPUT LOAD. it may, by coincidence, always be an integer ...or when it should be a decimal, and it rounds, no one has cared enough to comment ?!)

both in VBA code and as a calculated control value in a report, the same weirdness occurs.
anytime INPUT LOAD has a decimal and i multiply (QTY * INPUT_LOAD), the calculated result is something consistently like this:

4 * 2.4 = 190000038146973 ( !!! ? )

Field Data type issues I'm certain. Yes ?


as always,
with appreciation in advance,
m.

[jojowhite]

it is certainly a bug.
you should tell (feedback) microsoft about it.


meantime, will converting both field to Dec in your calculation
solve this:

CDec([QTY]) * CDec([INPUT_LOAD])

[June7]

Testing in Access 2020.

In VBA and query, result is 9.60000038146973.

So something weird in 365.

I don't think field types were actually changed. In Access 2016 LargeNumber (BigInt) and Date/TimeExtended were added and Memo and Text were renamed to LongText and ShortText.

[Gustav]

It certainly is not a bug, only demonstating the limitation of data type Single, which only should be used in very special cases.
Always use Currency for amounts, quantities, and similar, though - in this case - even Double will do:

? CLng(4) * CSng(2.4 )
 9.60000038146973 
 
? CCur(4) * CCur(2.4 )
 9.6 


? CLng(4) * CDbl(2.4 )
 9.6

[June7]

Gustav, OP says result they get is 190000038146973

[Edgar]

Can't reproduce that number. I get what the others get: an approximation, which is a result of dealing with Single data type. Do not use that data type if you need precision, that data type is used when you're dealing with stuff from which you don't really need accurate measurements. Simulations, visuals, 3d rendering, animation, experiments, quick calculations, etc. Cast to double, which is what VBA will do anyway with the result.

[Gustav]

Sorry, missed that.
Can't replicate that integer number. Something else is probably going on.

[mike60smart]

I get the same as Gustav.
Can you upload a copy of the database?