We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Replace string in Access Table

NO_CARRIER
NO_CARRIER asked
on
Medium Priority
503 Views
Last Modified: 2013-11-27
Some values in a column of my table end with 'CR'.
For example
1550.84CR

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

I would also like to then convert this column from TEXT to CURRENCY.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Oops- to precede with a hyphen:

UPDATE YourTable
SET YourField ="-" &  Replace(YourField, "CR","")
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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..
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
NO_CARRIER,

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"

Author

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)

Commented:
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.

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.