replace character in one field with contents of another field

[Chip]

I have a hyperlink field that I am using to link records
to images. To make it short, what I need to do is replace
a character ($) in part of my field with the contents of
the ID field. The result should be as follows:

My field currently reads "$_TN.jpg" and the result should
be "12_TN.jpg" where "12" is the contents of the ID field
in that record.

 

[Joe Fallon]

Make a backup of your mdb file and do some testing on it.
(I guarantee you will make a mistake and you do NOT want your real data
destroyed!)

All you need to do is write an Update query.

As a learning exercise - start with a Select query and add various computed
columns.
One might be the first 2 characters of your ID field. (Use Left([ID],2) for
that.)
Another might be the 2nd character plus the rest of the string - Use
Mid([fieldname],2) for this one.
Then the 3rd calculated field could be the combination of the first two.
Left([ID],2) & Mid([fieldname],2)

The 3rd field should be the final value you are looking for.

Now add criteria to the query so that only those records that start with $
are selected.

The screen should look very close to what you want.

The Update To expression you need is your 3rd computed value so copy it to
the clipboard.
Change the Select Query to an Update query and paste your expression in the
Update To value under your field that you want to update.

Then run it.
(This is where you thank me for having you make a backup of your mdb file.)

After you correct your mistakes, make another copy and try it again.

When you are sure you did it right, then do it on your real data.

Good luck!