Access 2016 DLookUp systax

Larry Fisackerly
Larry Fisackerly used Ask the Experts™
on
I have table tbl_Master Contacts Mailable with contact fields including Zip5 (zip codes).

I have table tbl_Counties Zip Codes with Zip Codes and Counties.

I need to use Access 2016 DLoopUP to add the counties from tbl_counties Zip Codes to the matching Zip5 in tbl_Master Contacts Mailable.

I get the following error: The expression entered contains invalid syntax. You may have entered an operand without an operator.”

find_county:DLookUp (“[County]”,”tbl_Counties Zip Codes”,”[Zip Code]='"&[Zip5]""')

What is the proper coding?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Rather than using a dLookup, why not simply create a query that looks something like:

SELECT MC.*, ZC.County
FROM tbl_Master_Contacts as MC
LEFT JOIN tbl_Zip_Codes as ZC on MC.Zip5 = ZC.Zip5

DLOOKUP is generally time consuming and not nearly as efficient as using a query.

but if you insist on using the DLOOKUP:

find_county:DLookUp (“[County]”,”tbl_Counties Zip Codes”,”[Zip Code]='" & [Zip5] & "'")

I assume this is in a query, not as the control source of a control on a form.  You might consider putting this DLOOKUP in the Control Source of a control on a form if you are using it in a form.  That way, you would not have to compute it for every record in the recordset, only those records which you actually land on.

Author

Commented:
I pasted the DLoopUP code you sent back. I got the same error. What now?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Try using brackets and true double quotes (no pasting):

find_county: DLookUp ("[County]","[tbl_Counties Zip Codes]","[Zip Code] = '" & [Zip5] & ""')

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
That worked. Thanks.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Larry,

Don't forget to close out the question.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Question is answered.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial