Inserting a Decimal & Comma

[tim]

I get a text and import into a Access table. Several of the fields contain
dollar amounts that are in this format; 100023. I need to update these
fields by inserting a decimal point two places from the right and a comma
separator. I am importing these fields as text as they are not going to be
used for calculation, but they will be exported into an Excel spreadsheet.



Thanks for any help,

Tim

Access 2000

 

[Guest]

Try this

Format(FieldName / 100 ,"#,###.00")

 

[John Spencer]

You can run an update query against the field and update it to

IIF(Len(TheField & "")>2, Format(Left(TheField,Len(theField)-2),"#,###") &
"." & Right(TheField,2),TheField)

Of course, the formula is simpler if you know that the field is always going
to be 6 characters long and will always have a value.

 

[tim]

That works great!

Thank you,
Tim

You can run an update query against the field and update it to

IIF(Len(TheField & "")>2, Format(Left(TheField,Len(theField)-2),"#,###") &
"." & Right(TheField,2),TheField)

Of course, the formula is simpler if you know that the field is always going
to be 6 characters long and will always have a value.