Inserting a Decimal & Comma

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tim - 17 Nov 2005 15:54 GMT

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

Reply to this Message

Ofer - 17 Nov 2005 16:38 GMT

Try this

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

Signature

I hope that helped
Good Luck

> 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
[quoted text clipped - 7 lines]
>
> Access 2000

Reply to this Message

John Spencer - 17 Nov 2005 17:08 GMT

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.

>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
[quoted text clipped - 7 lines]
>
> Access 2000

Reply to this Message

tim - 17 Nov 2005 18:05 GMT

That works great!

Thank you,
Tim

> You can run an update query against the field and update it to
>
[quoted text clipped - 15 lines]
> >
> > Access 2000

Reply to this Message