Error Handling in a DLookup Statement

Todd West
Todd West used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer

Commented:
Anytime you are trying to troubleshoot compound code, fix the basics before adding more too it.  Try fixing the DLookUp first so that it works:
=DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = Nz([Primary],'Not Assigned'))

Open in new window

Then add the NZ():
=NZ(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = Nz([Primary],'Not Assigned')),0)

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
At first your problem is not with Dlookup..the real issue is the lack of a key in the most important field...you simply can't have a table named Staff which depends as entity to a key field ID and have the field carrying null values.
Better fix this issue and then handle the problem ( which will vanish)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If the ID is not found, and DLookup returns Null, you can use Nz to supply "Not assigned", not a zero:

=Nz(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = " & [Primary] & ""), "Not Assigned"))

Open in new window

Todd WestBudget Analyst

Author

Commented:
I didn't explain statement very well.  The table staff uses an auto-numbered field 'ID" as its primary key.  Every ID has a staff members first and last named assigned.  I have a different table that has fields 'Primary' and 'Alternate" to identify what staff have been assigned to an issue by using the Staff.ID.  What I am trying to do now is populate a form by pulling the names from the staff table based on the values saved in my issues table.  IT is the primary and/or alternate fields from the issues table where a null may exist.  

I hope this makes sense.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then it should be:

=Nz(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[Primary] = " & [ID] & ""), "Not Assigned")

Open in new window

Mark EdwardsChief Technology Officer

Commented:
OK, so the DLookup criteria string needs to get the value of the [ID] field (a field in the form's current record or form control) inserted into the criteria string, then the dlookup runs?
If it isn't, then where is [ID] coming from?

Also, I see two opening parentheses in Gustav's formula, but three closing Parentheses.  My gut tells me it isn't going to work.
Todd WestBudget Analyst

Author

Commented:
Hi Mark, you are correct, I have two hidden fields named Primary and Alternate on the form that contain the Primary Key  (ID) from the Staff table to identify who is assigned.  I do my DLookup from the staff table using the hidden fields.
Mark EdwardsChief Technology Officer

Commented:
Sorry, didn't see Gustav's second post with the corrected formula.
Do things work now?
Todd WestBudget Analyst

Author

Commented:
Not quite there, now I am getting a value of Not Assign even when a staff member has been assigned.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then double-check your data.
"Not Assigned" will only be returned if ID has no matching Primary.
Mark EdwardsChief Technology Officer

Commented:
Double-check to make sure that the value for [ID] is a valid value when you test, and that all your data types and assumptions are correct.
What you are expecting and what you are asking may be two different things.
Mark EdwardsChief Technology Officer

Commented:
Remember that DLookup will only give you the value in the first record that it comes across that meets the criteria, and will return a Null (and thus the default NZ() value) if it finds no match.
The computer's not lying.... you're probably telling it one thing while expecting another.
Mark EdwardsChief Technology Officer

Commented:
You could try a test by hardcoding the value of [ID] into the criteria string (a value that you KNOW will return a name) and see if it gives you what you are expecting.
If it does, then you know it's the value of [ID] that you are feeding the formula.
Todd WestBudget Analyst

Author

Commented:
FIrst, thank you for your patience, as I am sure you can tell I am a novice at this.

Now I get the value not assigned for all records.  I have attached screen snips the show the value of the 'Primary' field to the right of the Assigned To (P) label.  One shot shows a null value that returns Not Assigned, the second shows a value of 7 that I want to return the first and last names of the staff member identified by Staff.ID = 7.

Thank you again!
Primary.PNG
Not-Assigned.PNG
Todd WestBudget Analyst

Author

Commented:
Great Idea about hard-coding the value, i will give that a try.
Mark EdwardsChief Technology Officer

Commented:
OK  Your DLookUp formula says that you are going to the Staff table and wanting to return the first and last names of a person who is in a record with a [Primary] field value equal to the value of [ID] in the form.

Correct?
Todd WestBudget Analyst

Author

Commented:
That is correct
Todd WestBudget Analyst

Author

Commented:
or return not assigned when the field I call Primary is null
Mark EdwardsChief Technology Officer

Commented:
If [ID] is a value, or a Null, you need to be aware of one thing:  A Null is not equal to a Null.  i.e. Null<>Null.
If [ID] is Null, then you will NOT get a record where [Primary] Is Null.

However, "" = "".
Mark EdwardsChief Technology Officer

Commented:
Are you getting expected results where [ID] is a positive numeric value that exists in [Primary]?
Todd WestBudget Analyst

Author

Commented:
ID is never null, it is the Primary Key for the staff table, Primary might be null as it is the foreign key that ties the staff assigned value to the staff table allowing me to do the lookup for the name.  AT least that is what I think i am doing
Mark EdwardsChief Technology Officer

Commented:
If you want to retrieve records where [Primary] is Null, then you need:
"[Primary] Is Null

Open in new window

Not:
"[Primary] = "

Open in new window

Todd WestBudget Analyst

Author

Commented:
NO, I am getting not assigned for any value in the Primary Field
Mark EdwardsChief Technology Officer

Commented:
OK.  Are both [ID] and [Primary] in the [Staff] table?  I think we need a little more background on your setup here...
Mark EdwardsChief Technology Officer

Commented:
A result of "Not Assigned" means that your DLookup is NOT finding ANY record in the Staff table where [Primary] =  the value of [ID].
Mark EdwardsChief Technology Officer

Commented:
Try hardcoding a valid value of [ID] that you KNOW is in [Primary].
Todd WestBudget Analyst

Author

Commented:
I apologize, I am sure I am complicating this for you with my descriptions.  When Text Box named Primary has no value (Null) I would like the text box field named Assigned to (P) to show "Not Assigned".  When the text box named Primary contains a value (equal to the primary key of the staff member assigned to the issue) I want the Assign to (P) text box to display their name.
Mark EdwardsChief Technology Officer

Commented:
OK you have 2 textboxes, 1 named "Primary", the other "Assign to (P)" textbox.
Your DLookup formula must be your ControlSource for your "Assign To (P)" textbox.
"Primary" has the value of "ID" in it?
Your formula is assigning the value of the control/field [ID] to your criteria.  Where is it in this scenario?
Mark EdwardsChief Technology Officer

Commented:
If there is no [ID] and you are not getting a value for [ID], then your criteria will always look like:
"[Primary] = "

Open in new window

which will ALWAYS result in nothing found, so default "Not Assigned".
Mark EdwardsChief Technology Officer

Commented:
Do you have a textbox named "ID" with [ID] as the controlsource, or are you expecting [ID] to come from the underlying recordset?  Is [ID] in the underlying recordset?
Mark EdwardsChief Technology Officer

Commented:
Try putting an invisible textbox with [ID] as the controlsource and have your DLookup function pull from the textbox.
Todd WestBudget Analyst

Author

Commented:
[ID] as textbox field from the table 'staff' is not in my form, only the two fields 'Primary' and 'Alternate' which get their values from an 'Issues' table where the values are either null or equal to [ID} for a staff member.  I had the DLookup working perfectly, it was only when I tried to replace the #ERROR I would get when no staff member had yet been assigned to the issue.
Mark EdwardsChief Technology Officer

Commented:
OK.  Your DLookup is trying to pull a value from something (field or control) called [ID] and put it in your criteria string.  Where is "[ID]"?
Mark EdwardsChief Technology Officer

Commented:
I'm sorry.  Let's go back to your original DLookup and forget Gustav's solution for a minute:
=Nz(DLookUp("[First Name] & "" "" & [Last Name]","Staff","[ID] = " & Nz([Primary],"Not Assigned") & "'"),0 )

Open in new window


… my bad... :-(
Mark EdwardsChief Technology Officer
Commented:
See if this works:
=Nz(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = " & Nz([Primary],0)),"Not Assigned")

Open in new window

Chief Technology Officer
Commented:
In the above formula, you get "Not Assigned" if [Primary] is null as the NZ() gives you 0 and thus a criteria string of "[ID]=0".
Is there any other condition/result you need?
Todd WestBudget Analyst

Author

Commented:
WORKS PERFECTLY!!! Thank you so much for your patience and expertise.
Mark EdwardsChief Technology Officer

Commented:
Sorry for the confusion....  but that's the way things go sometimes.
Glad you got it working!
Todd WestBudget Analyst

Author

Commented:
Thank you everyone for your time and patience helping me!
Mark EdwardsChief Technology Officer

Commented:
Glad I was able to help, after messing around for so long....

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