Number Format

[ploddinggaltn]

I am importing a txt file into an access table. I'm struggling with how to
format the values in one column from no format to a format that includes a
decimal place and comma. This is the format that I need but when I change
the format to add the decimal, Access adds two zeros plus the decimal at the
end of the number....here is a sample of how I need the numbers to display.
Thank you

15450 to 154.50
142985 to 1,429.85
9600 to 96.00

 

[John Spencer]

You have to divide by 100 and then apply the format.

It would probably be safest to leave the value as is and then when you need to
use it always use an expression.
SomeField / 100

IF you are always importing the txt file into an empty table you can use an
query update the value. Set the value to [SomeField]/100 in the update query.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Klatuu]

As John said, dividing by 100 on the intial import will get you the correct
number assuming, for example, that 142985 is actually 1429.85 expressed
without a decimal point; however, your comment about including the thousand
separator (comma) is irrelevant. Numeric data is not stored with any
formatting. That is done when you display a number on a form or report, or
exported to an external format. You normally use either a control's Format
property or the VBA Format function.