We help IT Professionals succeed at work.

Data Type Mismatch in Criteria Expression in Access

loepem
loepem asked
on
1,119 Views
Last Modified: 2013-11-28
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!
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

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

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23629500.html?cid=236#a22189031

are you creating a new one..

CERTIFIED EXPERT
Top Expert 2016

Commented:
which combo box is showing 0 ?

Author

Commented:
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.

Author

Commented:
The Unit combo box.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<cap - You can handle this one...>
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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!
CERTIFIED EXPERT
Top Expert 2016

Commented:
replace all the codes

Author

Commented:
Thanks again! I'm sure you'll be hearing from me soon again.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.