DLookup Multiple Criteria Problem

[Brendan Wolf]

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:

 

Private Sub Subverbs_GotFocus()
'When the Subverbs gets focus it looks up proper subverbs from Authority_Lookup table
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 &" "'")
End Sub

It works perfect with just the one criteria, but not with two or three more comboboxes as criteria.

The table is AUTHORITY_LOOKU P 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!

[Stewart Ross]

Hi Brendan. You need to provide a WHERE clause for the Dlookup which ANDs the separate criteria together, like this:

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.

-Stewart

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
Me.Clm_Rep_Type