Start Free Trial

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!

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)

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

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.

Then it should be:

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

Open in new window

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.

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.

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

ASKER

Not quite there, now I am getting a value of Not Assign even when a staff member has been assigned.

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

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.

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.

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.

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

ASKER

Great Idea about hard-coding the value, i will give that a try.

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?

ASKER

That is correct

ASKER

or return not assigned when the field I call Primary is null

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, "" = "".

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

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

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

ASKER

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

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].

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

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.

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?

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".

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.

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.

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:

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

Open in new window

… my bad... :-(

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

Sorry for the confusion.... but that's the way things go sometimes.
Glad you got it working!

ASKER

Thank you everyone for your time and patience helping me!

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