stardustvega
Member
- Local time
- Today, 16:02
- 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:
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:
(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:
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.
ID | EventID | CustomerID | CustWedPart | TestField |
1 | 1 | 1 | 1 | cust1 |
2 | 1 | 2 | 2 | cust2 |
3 | 2 | 3 | 1 | cust3 |
4 | 2 | 4 | 2 | cust4 |
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.