Avatar of Todd West
Todd West
Flag for United States of America asked on

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]", "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!
Microsoft Access

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Mark Edwards

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 Tsioumpris

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)
Gustav Brock

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Todd West

ASKER
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.
Gustav Brock

Then it should be:

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

Open in new window

Mark Edwards

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Todd West

ASKER
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 Edwards

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

ASKER
Not quite there, now I am getting a value of Not Assign even when a staff member has been assigned.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gustav Brock

Then double-check your data.
"Not Assigned" will only be returned if ID has no matching Primary.
Mark Edwards

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 Edwards

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

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 West

ASKER
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 West

ASKER
Great Idea about hard-coding the value, i will give that a try.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Edwards

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 West

ASKER
That is correct
Todd West

ASKER
or return not assigned when the field I call Primary is null
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

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 Edwards

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

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mark Edwards

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 West

ASKER
NO, I am getting not assigned for any value in the Primary Field
Mark Edwards

OK.  Are both [ID] and [Primary] in the [Staff] table?  I think we need a little more background on your setup here...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

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 Edwards

Try hardcoding a valid value of [ID] that you KNOW is in [Primary].
Todd West

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Edwards

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 Edwards

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 Edwards

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

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

ASKER
[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 Edwards

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]"?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Edwards

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... :-(
SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Todd West

ASKER
WORKS PERFECTLY!!! Thank you so much for your patience and expertise.
Mark Edwards

Sorry for the confusion....  but that's the way things go sometimes.
Glad you got it working!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Todd West

ASKER
Thank you everyone for your time and patience helping me!
Mark Edwards

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