• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

How design query to update field values in a table

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?
  • 2
1 Solution
Dale FyeCommented:
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
SteveL13Author Commented:
Perfect as always.  Thank you!
Dale FyeCommented:
You are welcome.  Glad I could help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now