Dlookup is driving me crazy (1 Viewer)

dkmoreland · Dec 7, 2017

Here's the scenario:

The Input form is tied to the Input table, which holds an Autonumber ID field. If the value of the Accept/Recheck field is equal to Recheck, a record is created in the Non Conformance table that includes the ID and an NCR number.

What I am trying to do is display on the Input form (in a text box) the NCR number (from Non Conformance table) that corresponds to the id number for the current record. Note that not all records in the Input Table will create a record in the Conformance table.

Here's the SQL for the query:

SELECT Input.ID AS Input_ID, Input.[Accept/Recheck], [Non Conformance].ID AS [Non Conformance_ID], [Non Conformance].[NCR#]
FROM [Non Conformance] INNER JOIN [Input] ON [Non Conformance].ID = Input.ID
WHERE (((Input.[Accept/Recheck])="Recheck"));

Here is the Dlookup I have in the Control source for the textbox:

=DLookUp("NCR#","Input Query3","ID=" & [Input].[id])

I am getting a #Name error and do not know why.

Can anyone offer any insight? Also, do I need to wrap this with an Nz function to take care of instances in which there is no NCR number?

Thanks in advance

jleach · Dec 7, 2017

Try wrapping your field name and table in square brackets (or better yet, don't use special chars in field/object names)

=DLookup("[NCR#]", "[Input Query3]", "ID=" & [Input].[ID]

dkmoreland · Dec 7, 2017

Thanks for the suggestion but I had already tried that. I tried it again to no avail - still getting the #name error.

I agree completely about not using special characters in field names. I didn't create this system - I inherited the problems.

Any other ideas?

jleach · Dec 7, 2017

Try running it in the immediate window:

Code:

?DLookup("[NCR#]", "[Input Query3]", "ID = 12345")

Use an ID you know works. This will make sure you have the syntax correct, and may provide a more meaningful error message. After that, try putting the ID into a variable prior to using it:

Code:

Dim ID As Long
ID = Input.ID '(what is input, anyway?)
Debug.Print DLookup("[NCR#]", "[Input Query3]", "ID = " & ID)

That'll make sure you're applying the variable correctly and will give you a chance to proof the input before trying to put it into DLookup.

Hopefully between those you can spot the error.

plog · Dec 7, 2017

Divide and conquer and identify the problem:

=DLookUp("[NCR#]","[Input Query3]")

dkmoreland · Dec 7, 2017

So I used this:

=Nz(DLookUp("[NCR#]","[Input Query3]","ID = 291527"),"None")

It now displays an NCR number on the record for that ID but it is the wrong one. It shows the very first NCR number in the query.

When I tried this:
=DLookUp("[NCR#]","[Input Query3]")

I got back the first NCR record in the query on every single record in the input table, whether it was in the query results or not.

I just have to figure out how to match up the ids with the NCR. They are matched up in the query results. I just have to get the criteria portion of the Dlookup right.

Minty · Dec 7, 2017

Is your ID a number or number stored in a text field?

plog · Dec 7, 2017

When I tried this:
=DLookUp("[NCR#]","[Input Query3]")...

Good, we now have a working version. Now its all about submitting the correct criteria to get the specific result.

You want to search by the ID field. When you run [Input Query 3] is the [ID] field's values left or right justified?

dkmoreland · Dec 7, 2017

The ID field is an Autonumber field in the Input table. In the Non Conformance table, it is a number field.

The values in the field when I run Input Query3 are right justified.

plog · Dec 7, 2017

How many [ID] fields does [Input Query 3] have?

Can you post the SQL of [Input Query 3]? Actually, can you post your database?

dkmoreland · Dec 7, 2017

I can't post the database but here is the SQL for the query:

SELECT Input.ID AS Input_ID, Input.[Accept/Recheck], [Non Conformance].ID AS [Non Conformance_ID], [Non Conformance].[NCR#]
FROM [Non Conformance] INNER JOIN [Input] ON [Non Conformance].ID = Input.ID
WHERE ((([Non Conformance].[NCR#]) Is Not Null));

The query is pulling the ids from both tables just to make sure I am pulling the right info.

Here's my latest attempt with the DLookup:

=Nz(DLookUp("NCR#","Input Query3","ID=" & [Input_ID].[id]),"None")

I know this is something simple that is escaping my detection.

Minty · Dec 7, 2017

Close but no cigar.

In your query you have aliased the ID field as Input_ID so you should be looking for;

Code:

=Nz(DLookUp("NCR#","Input Query3","[COLOR="Red"]Input_ID[/COLOR] = " & [Input_ID].[id]),"None")

Does that make sense ?

plog · Dec 7, 2017

I know this is something simple that is escaping my detection.

Escaping is exactly what you are doing wrong. As someone said before, when you use non-alphanumeric characters (#, space, etc.) in your table/field names; you need to put brackets around them so that they work when you write code. Of course the absolute correct thing to do is not use those characters in the first place.

Minty's code is probably right except for that.

dkmoreland · Dec 7, 2017

Minty said:
Close but no cigar.

In your query you have aliased the ID field as Input_ID so you should be looking for;

Code:

=Nz(DLookUp("NCR#","Input Query3","[COLOR="Red"]Input_ID[/COLOR] = " & [Input_ID].[id]),"None")

Does that make sense ?

It does make sense! However, no joy. Running it in the immediate window gets me a compile error - external name not defined.

This is what I ran in the immediate window.

?Nz(DLookUp("[NCR#]","Input Query3","Input_ID = " & [Input_ID].[id]),"None")

I know using that pound sign in the field name is a bad idea. But I didn't create this system so I have to work around it.

Minty · Dec 7, 2017

In the immediate window it won't know what [Input_ID].[id] is as that presumably refers to a field on your form.

You would need to use

Code:

?Nz(DLookUp("NCR#","Input Query3","Input_ID = 291527"),"None")

dkmoreland · Dec 7, 2017

Minty said:
In the immediate window it won't know what [Input_ID].[id] is as that presumably refers to a field on your form.

You would need to use

Code:

?Nz(DLookUp("NCR#","Input Query3","Input_ID = 291527"),"None")

When I run this in the immediate window and in the control source, it brings back what I need.

But when I try to expand the scope beyond the single record, I get the error.

MarkK · Dec 7, 2017

dkmoreland said:
...expand the scope beyond the single record...

What does this mean? Can you demonstrate this scope expansion?
Mark

dkmoreland · Dec 7, 2017

MarkK said:
What does this mean? Can you demonstrate this scope expansion?
Mark

I need to display the corresponding NCR number for whatever id is the current record on the form. And if there is no NCR, I need the NCR field to say so, which is why I'm using the NZ function.

=Nz(DLookUp("[NCR#]","Input Query1","ID = " & [Input Query1].[id]),"None")

Modified the query in hopes of simplifying it.
SELECT [Non Conformance].[NCR#], [Non Conformance].ID, Input.[Accept/Recheck]
FROM [Non Conformance] INNER JOIN [Input] ON [Non Conformance].ID = Input.ID
WHERE ((([Non Conformance].[NCR#]) Is Not Null) AND (([Non Conformance].ID)=[Input].[ID]));

dkmoreland · Dec 7, 2017

I got it working! I ended up with this:

=Nz(DLookUp("[Input Query1]![NCR#]","Input Query1","ID=" & [Forms]![input form]![id]),"None")

I just needed to pull the id from the form, instead of the query, to get the current record.

Thanks to everyone for helping me figure this out.

MarkK · Dec 7, 2017

Way to go, and thanks for posting back with your success.
Cheers,

Dlookup is driving me crazy (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Banishment Pending

Registered User.

AWF VIP

Banishment Pending

Registered User.

Banishment Pending

Registered User.

AWF VIP

Banishment Pending

Registered User.

AWF VIP

Registered User.

bit cruncher

Registered User.

Registered User.

bit cruncher

Similar threads

Users who are viewing this thread