• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Replace string in Access Table

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.
0
NO_CARRIER
Asked:
NO_CARRIER
  • 3
  • 3
  • 2
2 Solutions
 
mbizupCommented:
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.
0
 
mbizupCommented:
Oops- to precede with a hyphen:

UPDATE YourTable
SET YourField ="-" &  Replace(YourField, "CR","")
0
 
GRayLCommented:
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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..
0
 
mbizupCommented:
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"
0
 
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)
0
 
GRayLCommented:
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.
0
 
GRayLCommented:
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.
0

Featured Post

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.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now