troubleshooting Question

Error Handling in a DLookup Statement

Avatar of Todd West
Todd WestFlag for United States of America asked on
Microsoft Access
40 Comments2 Solutions78 ViewsLast Modified:
I have a DLookup statement that was working fine; however some records have null values in the field being looked up.  I originally attempted to add error handling with an IIf(IsNull to the DLookup but found another statement that seemed to be much easier.  The example I found looked like this:
= Nz(DLookup("[Commission]", "Employee", "[EMPNO] = '" & nz([EMPNO],"") & "'"),0 )

My DLookup statements looks like this but doesn't work:
=Nz(DLookUp("[First Name] & "" "" & [Last Name]","Staff","[ID] = " & Nz([Primary],"Not Assigned") & "'"),0 )

I feel like I am missing something that should be obvious but have tried to resolve for a couple hours with no success.  Can anyone identify my error or provide a better method for my lookup?

Thank you!
Mark Edwards
Chief Technology Officer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 40 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 40 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros