Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Importing / Linking / September 2004

Tip: Looking for answers? Try searching our database.

replace character in one field with contents of another field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chip - 02 Sep 2004 19:47 GMT
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 - 04 Sep 2004 03:39 GMT
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!

Signature

Joe Fallon
Access MVP

>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
[quoted text clipped - 4 lines]
> be "12_TN.jpg" where "12" is the contents of the ID field
> in that record.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.