Error Handling in a DLookup Statement
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]",
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!
= Nz(DLookup("[Commission]",
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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'))
=NZ(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = Nz([Primary],'Not Assigned')),0)
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)
Better fix this issue and then handle the problem ( which will vanish)
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"))
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.
I hope this makes sense.
Then it should be:
=Nz(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[Primary] = " & [ID] & ""), "Not Assigned")
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.
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.
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.
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.
What you are expecting and what you are asking may be two different things.
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.
The computer's not lying.... you're probably telling it one thing while expecting another.
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.
If it does, then you know it's the value of [ID] that you are feeding the formula.
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
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
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?
Correct?
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, "" = "".
If [ID] is Null, then you will NOT get a record where [Primary] Is Null.
However, "" = "".
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
If you want to retrieve records where [Primary] is Null, then you need:
"[Primary] Is Null
"[Primary] = "
OK. Are both [ID] and [Primary] in the [Staff] table? I think we need a little more background on your setup here...
A result of "Not Assigned" means that your DLookup is NOT finding ANY record in the Staff table where [Primary] = the value of [ID].
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.
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?
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?
If there is no [ID] and you are not getting a value for [ID], then your criteria will always look like:
"[Primary] = "
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?
Try putting an invisible textbox with [ID] as the controlsource and have your DLookup function pull from the textbox.
[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.
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]"?
I'm sorry. Let's go back to your original DLookup and forget Gustav's solution for a minute:
… my bad... :-(
=Nz(DLookUp("[First Name] & "" "" & [Last Name]","Staff","[ID] = " & Nz([Primary],"Not Assigned") & "'"),0 )
… my bad... :-(
See if this works:
=Nz(DLookUp("[First Name] & ' ' & [Last Name]","Staff","[ID] = " & Nz([Primary],0)),"Not Assigned")
Premium Content
You need an Expert Office subscription to comment.Start Free Trial