By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

DLookup Multiple Criteria Problem

P: n/a
Happy Halloween all,

I have been struggling at work with a DLookup using multiple criteria. I would like a text box to display the results of a DLookup based on the values selected in three comboboxes and the table that runs them all. Here is my code:

Expand|Select|Wrap|Line Numbers
  3. Private Sub Subverbs_GotFocus()
  4. 'When the Subverbs gets focus it looks up proper subverbs from Authority_Lookup table
  5. Me.Subverbs = DLookup("[Subverbs]", "AUTHORITY_LOOKUP", "[Sub_Line]= '" & "Me.Employee_LOB.Value &" And "AUTHORITY_LOOKUP", "[Band_name]= '" & "& Me.Change_Title.Value &" And "AUTHORITY_LOOKUP", "[InsideOutside]= '" & "& Me.Clm_Rep_Type.Value &" "'")
  6. End Sub 
It works perfect with just the one criteria, but not with two or three more comboboxes as criteria.

The table is AUTHORITY_LOOKUP and the fields are sub_line, band_name, InsideOutside, and I wand me.subverbs to display the Subverbs field result based on the value in the comboboxes.

I would love any help! This is my first post and this looks like a great community.

Thank you so much!
Oct 29 '10 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
Hi Brendan. You need to provide a WHERE clause for the Dlookup which ANDs the separate criteria together, like this:

Expand|Select|Wrap|Line Numbers
  1. Me.Subverbs = DLookup("[Subverbs]", "AUTHORITY_LOOKUP", "[Sub_Line]= '" & Me.Employee_LOB.Value & "' AND [Band_name]= '" & Me.Change_Title.Value & "'  AND [InsideOutside]= '" & Me.Clm_Rep_Type.Value & "'")
It can be tricky to get the correct combination of items inside and outside each substring (the parts which are ampersanded together) with the ampersands in the correct places.

It is often better to build such a WHERE clause in code before you use it within the DLookup, to make debugging easier.


PS the Value property of a control is its default, so you can always refer to a control without it and still get the same result:

Me.Clm_Rep_Type.Value is equivalent to
Nov 1 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.