By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,001 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.

VBA DLookup with multiple criteria not functioning

P: 5
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
  1.  
  2.  
  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 
  7.  
  8.  
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+
3 Replies


gnawoncents
100+
P: 214
Try:

Expand|Select|Wrap|Line Numbers
  1. Me.Subverbs = DLookup("[Subverbs]", "AUTHORITY_LOOKUP", "[Sub_Line] = '" & [Employee_LOB] & _
  2.             "' AND [Band_name] = '" & [Change_Title] & _
  3.             "' AND [InsideOutside] = '" & [Clm_Rep_Type] & "'")
Of course, you will need to filter for valid inputs since you could easily have an invalid combination selected with multiple combo boxes feeding into one DLookup.
Oct 29 '10 #2

P: 5
Awesome Awesome! Thank you gnawoncents! It worked like a charm. I will of course have restrictions in place so there is not null combination.

Have a great weekend!
Oct 29 '10 #3

gnawoncents
100+
P: 214
I'm glad it worked for you. You were close, just needed some minor adjustments.
Oct 29 '10 #4

Post your reply

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