Solved DLookup in form with multiple criteria not returning expected value (1 Viewer)

stardustvega

Member
Local time
Today, 04:10
Joined
Feb 4, 2022
Messages
36
I am trying to display information in a form using DLookup based on multiple (in this case two) criteria. The form is linked to an event table but is referencing a related table (in this case, a junction table it shares with a customer table). For the purposes of troubleshooting, I'm using hard-coded values but I'm still getting an odd result. This is what the dummy table looks like:

IDEventIDCustomerIDCustWedPartTestField
1111cust1
2122cust2
3231cust3
4242cust4

In the form, I want to display the customer ID for the customer with a given EventID and a given CustWedPart. So, I've given my field a control source of:

Code:
=DLookUp("TestField","Junction Events-Customers","EventID = 2" And "CustWedPart = 2")

(Later, those numbers would be dynamic but right now, I just want to get it working.) I would expect this to return "cust4", but instead, it returns cust1.

Things I've tried so far:
  • Both EventID and CustWedPart are set to be numbers so I don't think it's caused by a number that's being stored as a string
  • If I use just one criteria, I get what I would expect to get. For instance:
Code:
=DLookUp("TestField","Junction Events-Customers","CustWedPart = 2")

Returns "cust2", which makes sense because it's the first matching result in the table.

I suspect that there's something wrong with my syntax but I can't seem to work out what it is.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:10
Joined
Oct 29, 2018
Messages
21,357
Try it this way:
Code:
=DLookup("TestField", "Junction Events-Customers", "EventID=2 And CustWedPart=2")
Hope that helps...
 

stardustvega

Member
Local time
Today, 04:10
Joined
Feb 4, 2022
Messages
36
Oh geez. Yeah, that works fine. I've been pouring over threads for the last hour and never noticed that the AND operator goes inside the quotes not outside it. Missing the forest for the trees. Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:10
Joined
Oct 29, 2018
Messages
21,357
Oh geez. Yeah, that works fine. I've been pouring over threads for the last hour and never noticed that the AND operator goes inside the quotes not outside it. Missing the forest for the trees. Thank you!
Happens to everyone. Glad we could assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom