Issue with DLookup when comparing strings in Query

Question

I am trying to use Dlookup to look up a value in a query using two criteria ("s" and "Error") to populate variable "SCount". s is an integer for shift number, and Error is a string. Both are fields in the query. The Dlookup is pulling from a query which should be strings for the data type, the query is pulling from a table where the fields are strings. So I am not sure why I am getting a mismatch error.

When I run the code I get the error "Run-time error '3464' Data type mismatch in criteria or expression". For the the Error in the SCount Dlookup.

Code

Dim Error As String
Dim i As Integer
Dim s As Integer
Dim n As Integer
Dim SCount As Variant
Dim sum As Double
sum = 0
i = 1
s = 1
n = 1
Do Until i > 5
If Not IsNull(DLookup("[Type of Error]", "RankedSumOfErrors", "[Ranking] = " & i)) Then
    Error = DLookup("[Type of Error]", "RankedSumOfErrors", "[Ranking] = " & i)
    Me.Controls("lbl" & i & "").Caption = Error
        Do Until s > 3
            SCount = DLookup("[SumOfNumber of Errors]", "ErrorsByShift", "[Shift] = " & s & " AND [Type of Error] = '" & Error & "'")
            Me.Controls("lbls" & s & "").Caption = SCount
            i = i + 1
            sum = sum + SCount
        Loop
            Me.Controls("Total" & i & "").Caption = sum
            sum = 0
Else
    Me.Controls("lbl" & i & "").Caption = "N/A"
    i = i + 1
End If

Loop

You shouldn't use Error as variable name - it is part of the VBA language. — Andre

Erik A Erik A · Accepted Answer · 2017-11-29T07:48:55

If the fields are strings in the query, they should be so as well in the DLookUp.

Add parentheses to your DLookUp to avoid the error:

SCount = DLookup("[SumOfNumber of Errors]", "ErrorsByShift", "[Shift] = '" & s & "' AND [Type of Error] = '" & Error & "'")

Issue with DLookup when comparing strings in Query

1 Answers