Update Part of String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have imported data into my Access database. I need to update only part of the value of a field. For instance I need to convert R Ankle to Right Ankle. Is there a way to do this through an update query. Thanks.
 
I have imported data into my Access database. I need to update only
part of the value of a field. For instance I need to convert R
Ankle to Right Ankle. Is there a way to do this through an update
query. Thanks.
Click to expand...

Yes, this can be done.
How depends upon which version of access you are using.
In Access 2000 or later you can use the Replace Function:
Update YourTable Set YourTable.FieldName = Replace([FieldName],"R
","Right ");

The above will work as long as the string does not contain the
combinaton of "r ankle" used within a different context, such as
"wrist or ankle".

In Access 97 how may depend upon whether the field consists ONLY of
two words, or if the 2 words can be in the beginning, middle, or end
of a longer string. And is it only R Ankle we're looking for, or other
words to be changed as well. You would use the Left, Mid, Right, and
InStr functions.

If you need more help, post back with more of the particulars.
 
I have imported data into my Access database. I need to update only part of the value of a field. For instance I need to convert R Ankle to Right Ankle. Is there a way to do this through an update query. Thanks.
Click to expand...

You need to pick the string apart and put it back together. For
example, you could use a Criterion of

LIKE "R *"

to select only those records which have R and a blank at the beginning
of the field (R Ankle, R Knee, R Elbow); change it to an Update query;
and update to

"Right" & Mid([fieldname], 2)

The Mid() function will extract the substring of the field from the
second byte on (in your example, " Ankle"), and this will be
concatenated with the word Right.
 
Back
Top