replace part of field with contents of another field

  • Thread starter Thread starter Chip
  • Start date Start date
C

Chip

I need to replace a wildcard character ($) in one field
with the contents of the ID field in the same record. My
database is 3000+ records, so doing this by hand is not an
option - or at least not a good one. For example:

My field currently reads "$_TN.jpg" and I want the result
to be "12_TN.jpg" where "12" is the value in the ID field
of the same record.
 
Assuming you're using Access 2002 or Access 2003, you should be able to use
the Replace function in an Update query. You'd want someting like
Replace([MyField], "$", [ID]).

This should also work in Access 2000, but if all the service packs haven't
been applied, sometimes the Replace function doesn't work in queries.
There's an easy work-around, though: write your own wrapper function that
uses the Replace function:

Function MyReplace(InputString As Variant, _
ChangeFrom As String, _
ChangeTo As String) As Variant

MyReplace = Replace(InputString, _
ChangeFrom, ChangeTo)

End Function

If you're using Access 97 or earlier, you'll have to write your own Replace
function.
 
Doug, You are WONDERFUL!! It worked perfectly. You have
no idea how much time you just saved me. THANK YOU!!!

-----Original Message-----
Assuming you're using Access 2002 or Access 2003, you should be able to use
the Replace function in an Update query. You'd want someting like
Replace([MyField], "$", [ID]).

This should also work in Access 2000, but if all the service packs haven't
been applied, sometimes the Replace function doesn't work in queries.
There's an easy work-around, though: write your own wrapper function that
uses the Replace function:

Function MyReplace(InputString As Variant, _
ChangeFrom As String, _
ChangeTo As String) As Variant

MyReplace = Replace(InputString, _
ChangeFrom, ChangeTo)

End Function

If you're using Access 97 or earlier, you'll have to write your own Replace
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Chip said:
I need to replace a wildcard character ($) in one field
with the contents of the ID field in the same record. My
database is 3000+ records, so doing this by hand is not an
option - or at least not a good one. For example:

My field currently reads "$_TN.jpg" and I want the result
to be "12_TN.jpg" where "12" is the value in the ID field
of the same record.
Click to expand...


.
Click to expand...
 
Back
Top