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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"HeliCaptKirk" <(E-Mail Removed)> wrote in message
news:90312A8A-EAE2-4869-890C-(E-Mail Removed)...
> 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
>
Similar Threads
