replace part of field with contents of another field

[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.

 

[Douglas J. Steele]

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.

 

[Guest]

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!)

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.

.