asked on

Data Type Mismatch in Criteria Expression in Access

I am creating a pop-up form to filter a report. I have created five drop down menus, however one of them is not working. The other four work perfectly but when I try to filter using the first one and error message comes up saying:
"Data type mismatch in criteria expression."
My guess is that in my code I have "Dim intCounter As Integer" and this works for all the rest of the fields which are all numbers or letters, but the one giving me problems includes a zero. That's just my guess. I'm really not sure.
Thanks!

Jim Horn

Yep. Mind reading. Man am I terrible at it.

Please copy-paste the code that executes 'filter using the first one' and returns this error.

loepem

ASKER

Private Sub Clear_Click()
Dim intCounter As Integer
For intCounter = 1 To 5
Me("Filter" & intCounter) = ""
Next
End Sub

Private Sub Close_Click()
DoCmd.Close acForm, Me.Form.Name

End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "AdminAC" 'Close the Air Cooler report.
DoCmd.Restore 'Restore the window size
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "AdminAC", A_PREVIEW 'Open Air Cooler report.
DoCmd.Maximize 'Maximize the report window
End Sub

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
'Stop
'Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
If intCounter = 1 Then
' strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Me("Filter" & intCounter) & " And "

Else
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
End If
Next

If strSQL <> "" Then
'Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

'Set the Filter property.
Reports![AdminAC].filter = strSQL
Reports![AdminAC].FilterOn = True
Else
Reports![AdminAC].FilterOn = False
End If

End Sub

Rey Obrero (Capricorn1)

loepem,
i already gave you a working form from this thread

https://www.experts-exchange.com/questions/23629500/Pop-Up-Form-to-Filter-Report.html?cid=236&anchorAnswerId=22189031#a22189031

are you creating a new one..

Rey Obrero (Capricorn1)

which combo box is showing 0 ?

loepem

ASKER

Yes I am and that's the problem I've been having. I have to create a couple of these. And I have used what you have given me and it's worked perfectly for another one, but I'm having troubles with this one.

loepem

ASKER

The Unit combo box.

Jim Horn

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

loepem

ASKER

the rowsource is: SELECT DISTINCT AirCooler.Unit FROM AirCooler ORDER BY [Unit];
the datatype is a text.

do i just add that to my code or replace what is there with this?
Sorry, this is my first time every using access!

Rey Obrero (Capricorn1)

replace all the codes

loepem

ASKER

Thanks again! I'm sure you'll be hearing from me soon again.