DLookup with 3 criteria - one of them boolean

[HeliCaptKirk]

I would like to know how to correctly concatenate the dlookup string. If I
do each criteria individually, it runs correctly, but obviously get the first
hit in the table. Every time I concatenate the three criteria, I get a "Type
Mismatch" error.
You all have been so helpful in the past. can you help me here:
Here's the code:

If Not IsNull(Me.ReviewGroupNum) Then
Dim AssignedQTR As String
Dim CurrentGroupNum As String
Dim CategoryField As String
Dim DeactivatedState As Boolean
DeactivatedState = True
CategoryField = "ReviewQuarter"
CurrentGroupNum = Me.Form.ReviewGroupNum.Value
AssignedQTR = DLookup("[ValuePrimary]", "tblCategoryList",
"[CategoryName] = '" & CategoryField & "'" And "[ValueSecondary] = '" &
CurrentGroupNum & "'" And "[Deactivated] <> '" & DeactivatedState & "'")
Me.ubtxtReviewQTR = AssignedQTR
Else
Me.ubtxtReviewQTR = ""
End If


FYI: If I do a code break point, I can see all the criteria values populate
with the correct criteria parameters. The system always fails on the Dlookup
line...
thanks.
Kirk

 

[Allen Browne]

The example below assumes that when you open your table in design view, the
fields have this data type:
- CategoryName Text
- ValueSecondary Number
- Deactivated Yes/No

If that's right, you need to build the WhereCondition string for DLookup()
as shown. We make it a separate string so that if it doesn't work you can
debug.print the string and see what's wrong (e.g. mismatched brackets, bad
delimiters, etc.)

I've changed the variable names to help you keep track of the data type of
each one. If the DLookup() doesn't find any match, it returns Null, so the
assigned quarter has to be a variant.

See if this helps:

Dim strWhere as String
Dim varAssignedQTR As Variant
Dim strCategoryField As String
Dim bDeactivatedState As Boolean

varAssignedQTR = Null
If Not IsNull(Me.ReviewGroupNum) Then
bDeactivatedState = True
strCategoryField = "ReviewQuarter"
strWhere = "([CategoryName] = """ & strCategoryField & _
""") AND ([ValueSecondary] = " & Me.CurrentGroupNum & _
") AND ([Deactivated] <> " & DeactivatedState & ")"
'Debug.Print strWhere
varAssignedQTR = DLookup("[ValuePrimary]", "tblCategoryList", strWhere)
End If
Me.ubtxtReviewQTR = varAssignedQTR

If the quotes don't make sense, see:
http://allenbrowne.com/casu-17.html

 

[Alex Dybenko]

Hi,
try this:
AssignedQTR = DLookup("[ValuePrimary]", "tblCategoryList",
"[CategoryName] = '" & CategoryField & "' And [ValueSecondary] = '" &
CurrentGroupNum & "' And [Deactivated] <> '" & DeactivatedState & "'")

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com