Preserving decimal places when converting numbers into strings in Access
Q: In my table, I have a numeric field which holds a number and shows it to 3 decimal places. Some values might be:
8.034
9.990
6.000
I need to convert this in a query to a text field, but when I do so, I lose the decimal places, so that the examples above come out in the query as:
"8.034"
"9.99"
"6"
I want the same format (i.e. 3 decimal places) in the query text field. How can I do it?
A: Here's the formula you need in your query:
NumAsText: CStr(Format([Number],"0.000"))
Replace [Number] with the name of your numeric field. This will work as a Select query, or as an Append or MakeTable if you prefer. The 'Format' function forces the three decimal places, and then the 'CStr' function converts the result into a string.