Access imported data rounding decimals down

[Lol999]

Not quite sure where to put this one so I settled for "General".

I've got a spreadsheet exporting data to an Access table. The spreadsheet is formatted to display 2 decimal places, i.e. 38.50

When it's copied via vba into an Access table though it is rounding down to 38.00

I've tried various number formats in the table but wonder if it's an Excel problem in that the vba is making assumptions as to how the data should be regarded.

Is there a method of ensuring the format of data written to a recordset?

Thanks, Lol

 

[pbaldy]

Rather than format, check the data type of the field in the table. I'm guessing it's Number/Integer or Long Integer, neither of which can hold a decimal. Try currency.

 

[Lol999]

Currency format worked but then I tried the following in Access:

Data Type: Number
Field size: Double
Format: Fixed

and it seems to work!

Thanks, Lol

 

[pbaldy]

Double will work. I avoid it because it's a floating point number and in certain circumstances you can get odd results where 2+2 does not equal 4, it equals 3.999999999 or something along those lines. Single has the same issue.

 

[Lol999]

Double will work. I avoid it because it's a floating point number and in certain circumstances you can get odd results where 2+2 does not equal 4, it equals 3.999999999 or something along those lines. Single has the same issue.

It works for now so it's good, thanks for your help.

 

[pbaldy]

No problem!

 

[The_Doc_Man]

OK, at least in theory, with nobody playing unusual formatting games, 1.0 + 1.0 will always equal 2.0 for floating - but 1.5 + 1.5 might NOT equal 3.0 because of rounding. That's because the string-to-number conversion with floating-point can handle trailing ".0" cases, but trailing non-zero fractions can get tripped up due to variations in the way that some folks / vendors implement their format conversions.

Concur that in some cases, there are better choices than floating-point. On my OpenVMS/Itaniums I never recall seeing this problem. On my Windows boxes, that dropped down to "rarely had the problem."

 

[The_Doc_Man]

Addition and subtraction are subject to catastrophic cancellation and loss of significance. The difference between two very close fractions is problematic in binary due to the simple fact that variables have capacities.

Multiplication and division have the very different problem that odd numbers in binary NEVER product even fractions. Therefore, when you have a divisor (or an inverted multiplier) for which one of the factors is an odd number, it is impossible to represent in binary. So when you run out of room for the mantissa, the truncation at the low end of the fraction forces the number to be an approximation.

Could be worse. Some years ago there was a problem with the old Intel Pentium (P5) chips where even an integer division would sometimes come up with the wrong answer. Turned out they used a short-cut in the chip and did a table lookup for certain cases. But they got some the answers wrong in the lookup table, so Windows had to do some things to disable integer division and replace it with a software simulation of division. Fortunately for them, it was something that could be placed in the HAL (Hardware Abstraction Layer) for the P5 until the corrected version of the chip came out.