changing Text to Currency

[Andrew Cheng]

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!

 

[Rick Brandt]

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!

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.

 

[Andrew]

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