Amounts with no Decimal & Leading 0's (000010014), Convert to Standard ($100.14)

[orbojeff]

My .csv file contains dollar amounts with no decimal point and leading
zeros: $100.14 is 000010014.

I would like to import this into a table and convert them to standard
dollar amounts; no leading zeros and decimal point.
When I choose "Currency" Format it removes the leading zeros, but add
"00" cents: $10014.00

Thanks
Jeff

 

[Duane Hookom]

It isn't at all clear if the actual amount is 100.14 or 10,014. Formats are
applied in controls that display on forms and reports.

You may need to run an update query to divide your currency amounts by 100.

 

[orbojeff]

Duane

The amounts come in as 0010014 and is $100.14, NOT $10,014.00.
Is there any way to get around this without using an additional query?
What about input mask?

If not can you help me with the query

Thanks
Jeff

 

[Duane Hookom]

A stored value is a stored value. Update it so that you are storing the
correct value.

 

[tina]

an input mask will not affect imported data. as Duane says, you should
update the data after inport so it's stored accurately in the table. for
your Update query, try

UPDATE TableName SET Table1.CurrencyFieldName =
[TableName].[CurrencyFieldName]/100;

substitute the correct table/field names, of course.

hth