Replace string in Access Table

Some values in a column of my table end with 'CR'.
For example

For each of these values, I would like to change the string to begin with a hyphen, and drop the CR.
for exampole

I would also like to then convert this column from TEXT to CURRENCY.
Who is Participating?
GRayLConnect With a Mentor Commented:
In the table create a field with datatype currency.  Then:

UPDATE tblValues SET fldCurrency=IIf(Right(fldText,2)="CR",-Val(fldText),Val(fldText))

Of course use your real table and field names.
For the first part of your question, use the following SQL:

UPDATE YourTable
SET YourField = Replace(YourField, "CR","-")

Change the table and field names so that they match your actual names.
Oops- to precede with a hyphen:

UPDATE YourTable
SET YourField ="-" &  Replace(YourField, "CR","")
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

NO_CARRIERAuthor Commented:
Gray how could I conver the field data type to currency via VBA?
This is a tab delimited file I'm importing (daily), which has CR (credit) in place of a negative symbol... so everytime it's imported, I'll need to 'clean' the data, then convert the field type to Currency..

Your UPDATE query works perfectly..
mbizupConnect With a Mentor Commented:

You didn't respond to my comment (first post, with correction in the second post). Did you try it?

To change the datatype of a field to currency in an Access database:

currentdb.Execute "ALTER TABLE YourTableName ALTER COLUMN YourColumn Currency"
NO_CARRIERAuthor Commented:
mbizup, the problem with the replace function is that not all records will always have a CR at the end, therefore not all records require a hyphen at the beginning.  Gray's solution got around this by adding an Iif function.  

Thank you for the alter table function, that works as expected.  I've also found that a field datatype can be change with simply Format([Field_Name],Currency)
I said you have to 'create' a new field of datatype Currency.  Open the table in design view, add the field and make the datatype Currency.    Use an appropriate name for the field and save.  Now when you update the currency field you may wish to delete the text field.
That does not change the datatype of the field, it makes the field it represents in a query or a form/report a Currency datatype - not the same thing.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.