Access imported data rounding decimals down (1 Viewer)

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

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.

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

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.

pbaldy said:

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.

Click to expand...

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

No problem!

It works for now so it's good

Click to expand...

Bad Attitude for a developer. When an expert tells you there is a problem if you do something one way and they offer you an alternative, you would be wise to take the advice even though it is free.

Do not confuse the Currency data type with the Currency format. The Currency data type is simply a scaled integer with four fixed decimal digits. Using it, 1+1 will ALWAYS = 2. However using single or double 1+1 will sometimes = something else. The floating point rounding error is tiny and you would never see it unless you were displaying more than 6 decimal digits but when you want to know if fldA = fldB and fldB was calculated, the two might not actually be equal due to the discrepancy way down in the decimal digits.

Of course if you need more than 4 digits of precision, you have no choice. But for the vast majority of data, Currency is the best choice and will not give you a problem the way the floating point types will.

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."

Actually, the problems generally occur with multiplication and division rather than addition and subtraction. Take a look at Luke's article at FMSINC.com titled "When Access math doesn't add up"

http://fmsinc.com/tpapers/math/index.html

In applications where I have needed more than 4 decimal places, I have implemented the "close enough for government work" method suggested in the article.

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.

Getting back to the entire reason for my response --

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

Click to expand...

When there is a known issue and a foolproof solution, why would you stick with the method that at some point could cause a problem?