Delete Query Problem

[Snoopy]

Dear All,

I have an access database with a price field. This contains a list of prices in the format $0.00. As this data has been imported it is contained within a text field. I have been rakking my brains trying to create a query to delete all the $ symbols from the prices, so that then these values can be held as currency rather than text

So far I have made about 30 failed attempts

please help if posss...

 

[namliam]

Try using the Replace function?!

Find it in the access help for details on how to use it!

Greetings from Amsterdam

 

[mhartman]

Hello

In your query, add a new column and use the MID function to extract and return everything from the dollar sign.

Regards
Mark

 

[Moniker]

Out of curiosity, I went into an existing DB on this end, made a field called "money" on an existing table, and set the type to be a text field. I went in to that field and typed in $10.00, $12.50, and $15.00 (three records). I then went back to design mode, and changed the type to currency, and voila, it was no longer text, but it kept all the values.

Replacing the "$" with "" will also work, but I guess I'm missing why changing the data type in design mode doesn't work for you.

 

[Snoopy]

Re

Thank you all very much!
In the end I used

UPDATE PartsList SET PartsList.Price = Mid([PartsList].[Price],2,10);

and that worked perfectly! Many thanks!