changing Text to Currency

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew Cheng - 20 Jan 2008 14:28 GMT

I have been supplied with data extracted from a third party database. I have
two CSV files for two different tables. I have also been given an Access
2003 database that the two CSV tables have been imported into and linked via
a query. The problem is that one of the fields should be a number with two
decimal place. It currently exists a text field in Access and display "AU
$" in front of the numbers. ie. I need to change the text field from "AU
$12.5" to a Currency field "12.5". Can't seem to find the way of dropping the
charactar "AU $" and then change the number to a currency. Please help!

Reply to this Message

Rick Brandt - 20 Jan 2008 14:35 GMT

>I have been supplied with data extracted from a third party database. I have
> two CSV files for two different tables. I have also been given an Access
[quoted text clipped - 4 lines]
> $12.5" to a Currency field "12.5". Can't seem to find the way of dropping the
> charactar "AU $" and then change the number to a currency. Please help!

First update the text field to chop off the first four characters...

UPDATE TableName
SET FieldName = Mid(FieldName, 5)

Once that is done you should be able to convert the field to Currency.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Andrew - 24 Jan 2008 00:54 GMT

I wanted to keep the original imported file the way it is, just in case if i
mess things up. So i have created a new form with exact same thing in the
table but instead of the a text [sale price] i have a currency [sale price]
with

control source = val (Mid([sale price], 5))

but i still get error, could you help?

Thanks

> >I have been supplied with data extracted from a third party database. I have
> > two CSV files for two different tables. I have also been given an Access
[quoted text clipped - 11 lines]
>
> Once that is done you should be able to convert the field to Currency.

Reply to this Message