A while back I tried to change a field from double to single. The result was the numbers somehow got corrupted. (2.33 could be 2.33000000001 for instance).
although my number fields just seem to add random digits onto the end of 3 decimal placed numbers..
They aren't random and they aren't corrupted. You are running into problems with differences in allowed precision between DOUBLE and SINGLE formats. SOMETIMES you get lucky and don't notice the difference. But you folks have run into the precision barrier.
Basically, on a PC, your hardware defines a SINGLE as a 32-bit quantity with a sign bit, some number of bits for an exponent, and the rest for a mantissa. I think it is [Sign-1 bit][Exponent-8 bits][Mantissa-23 bits] = [Single-32 bits]. The exact specification doesn't matter as much as the difference between this and a DOUBLE format. In the DOUBLE format, you have the same sign bit and exponent, but
32 more bits for the mantissa. ALL of the extra 32 bits are dedicated to the mantissa. ALL. Something like 55 bits.
In practical terms, the decimal equivalent of a SINGLE stops somewhere between six and seven decimal digits. A DOUBLE gives you 15 digits.
So when you do your conversion from DOUBLE to SINGLE, you remove bits or digits. If you have 23 bits of mantissa, then the smallest bit represents 0.000000119209289550781 of the total number. On the other hand, if you have a DOUBLE, you have 55 bits of mantissa, for which the smallest bit represents 2.77555756156289E-17 of the total number.
Where that leaves you is that if you count the digits, in RELATIVE terms you can accurately represent numbers to 0.1 parts per million (ppm) or 100 parts per billion (ppb) in a SINGLE, but in a DOUBLE you have parts per quadrillion or quintillion. (Note to my UK friends... I'm not sure that American names and UK names for these numbers match up in the exact number of decimals. I'm using American notation.) If you try to represent more digits than this from a SINGLE, you are getting a result because of working in decimal numbers, which are irrational with respect to binary.
This occurs because the SINGLE or DOUBLE number is a binary fraction but ... the binary equivalent of 0.10000 in decimal is a repeating binary fraction. Something like 0.001100110011.... (base 2) if I recall correctly. Don't even ask about fractions like 1/3 or such... they are HOPELESS! For the "it added random numbers" problem - they are not truly random. Once you ask for more digits than the format supports, you are seeing residuals from the conversion math creeping in because of the inexactitude of arbitrary decimal numbers. Trust me, you could get an incredibly large number of decimal digits out of a binary fraction past the 6th or 15th digit if you really wanted to. Like, at LEAST 23 digits out if SINGLE or 55 digits out of DOUBLE.
SO.... getting 2.33000000001 in SINGLE format when you converted 2.33 in DOUBLE format is actually lucky. What Access did is it converted the number for you based on the number of decimals you specified (I presume). But you should not have tried to see more than 2.3300000 (which is 0.1 ppm) for a SINGLE. And in that case, Access would have truncated the trailing zeroes back to 2.33 for you. The problem (in my mind) is therefore asking for more digits than are supported by the number format.
If you have a number in the range 100K or higher in SINGLE mode, you must remember that you can only accurately represent 100000.00 (0.1 ppm) or 1000000.0 (0.1 ppm) or 10000000. (0.1 ppm). Anything greater than 10,000,000, forget about decimal places. You don't have ANY for a SINGLE.
This is the misleading part of SINGLE/DOUBLE choices. If you have big numbers AND the decimal places still count, you need to use DOUBLE. Like, if your company's books track $100 million bucks, you got no cents if you use SINGLE for that amount. (Sorry for the pun... No I'm not!
)
