We help IT Professionals succeed at work.

How design query to update field values in a table

SteveL13 asked
I have a table with several records that have the following examples in a field named "Image":

B:\Dropbox\Business Documents\Database\Database Images\Tree.jpg
B:\Dropbox\Business Documents\Database\Database Images\Football.jpg
B:\Dropbox\Business Documents\Database\Database Images\Rainbow.jpg
B:\Dropbox\Business Documents\Database\Database Images\Flower.jpg

But I want to create an update query that will chance the text in the field to be a lookup from a table and the last part of the existing field value.

So if the lookup value is...

= DLookup("[ImageDirectory]", "tblDatabaseSetup")

Then I want the new field values to read:

The Looked Up Value\Tree.jpg
The Looked Up Value\Football.jpg
The Looked Up Value\Rainbow.jpg
The Looked Up Value\Flower.jpg

In other words the field should display the looked up value and keep the last part of the existing value for example Tree.jpg

How can I create this update query?
Watch Question

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
start with a select query:

SELECT [Image], DLookup("[ImageDirectory]", "tblDatabaseSetup") & mid([Image], instrrev([Image], "\")) as NewImage
FROM yourTable

If that givew you what you are looking for, then you can modify the query to an update query:

UPDATE yourtable
SET [Image] =  DLookup("[ImageDirectory]", "tblDatabaseSetup") & mid([Image], instrrev([Image], "\"))
I recommend to do a Public Function and use it in the SQL for the query

Public Function get_imgpath()
    get_imgpath=DLookup("[ImageDirectory]", "tblDatabaseSetup")
End Function


Perfect as always.  Thank you!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

You are welcome.  Glad I could help.