DLookUp on Update Query (1 Viewer)

fedebrin · Jul 16, 2018

Dear all,

Trying to use a DLookUp formula in an update query to clean up a code
There are 2 tables with the following:

MainTable which Contains the fields: MainDirtyCode and MainCleanedCode
CodeCleanUp which Contains the fields: DirtyCode and CleanedCode

Update Query to modify MainCleanedCode:

DLookUp("CleanedCode","CodeCleanUp","DirtyCode"="MainDirtyCode")

The query runs but I am not getting anything updated.
Your help is greatly appreciated.

pbaldy · Jul 17, 2018

What's the SQL of the query? I'd expect to be able to join those tables in the query rather than use DLookup().

MajP · Jul 17, 2018

agree with pbaldy. However the way you wrote that

Code:

"DirtyCode"="M ainDirtyCode"

it is going to look for DirtyCode that exactly equals the string "M ainDirtyCode"
It would be more like

Code:

"DirtyCode = '" & [MainDirtyCode] & "'"

Pat Hartman · Jul 17, 2018

DLookup() is an inefficient method. Just join the two tables on the "dirty" value and update the "clean" value.

DLookup() runs a separate query for each row and so cannot be optimized. If you have a thousand rows to update, A thousand queries will have to run. The join is a more efficient operation and lets the query engine pass the data only once.

fedebrin · Jul 17, 2018

Thank you all for your responses.

How would I go about a Union SQL? I am not familiar unfortunately with this language.

Thanks in advance,

pbaldy · Jul 17, 2018

In general terms it would look like:

UPDATE TargetTable
INNER JOIN SourceTable ON TargetTable.KeyField = SourceTable.KeyField
SET TargetTable.FieldName = SourceTable.FieldName

fedebrin · Jul 17, 2018

Thank you!

One thing I don't understand... you do not need to do the DlookUp and this Union query on the same QUery right?

pbaldy · Jul 17, 2018

For clarity, this is not a union query, this is an update query. No, you do not need the DLookup() with this. The join between the tables enables you to get the values from one table to put in the other.

fedebrin · Jul 17, 2018

Excellent! thank you so much

pbaldy · Jul 17, 2018

Happy to help!

DLookUp on Update Query (1 Viewer)

fedebrin

Registered User.

pbaldy

Wino Moderator

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

fedebrin

Registered User.

pbaldy

Wino Moderator

fedebrin

Registered User.

pbaldy

Wino Moderator

fedebrin

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread