Stumped: Data type mismatch in criteria expression.

**Zellax** · 08-25-2017, 04:55 PM

I loathe to ask others to troubleshoot stuff like this but I'm not seeing other options. If you see me doing things that seem weird just keep in mind I'm still ironing out basic testing of functionality.

I have a macro that does nothing else but calls this VBA global function:

Code:

Public Function SearchFunc()    
    On Error GoTo TestSearch_Err
    
    ' hard coded only for testing
    gstrOrderSearch = "251822"
    gstrRingSearch = "1"
    
    DoCmd.OpenForm "RingSearchResult", acNormal, "", "", , acNormal
    
TestSearch_Exit:
    Exit Function


TestSearch_Err:
    MsgBox Error$
    Resume TestSearch_Exit
    
End Function

The form this opens has a bunch of unbound text boxes and check boxes that I am trying to populate with an on-load event. Using breakpoints I have narrowed it down to the 'Me.FilterOn = True' statement. Why would it balk at that? it is a boolean value so how is that a data type mismatch? I am in fact copying that part out of working code elsewhere in the database and it works over there.

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
    Me.Filter = "(Order_Num = """ & gstrOrderSearch & """) AND (Ring_ID_No = """ & gstrRingSearch & """)"
    Me.FilterOn = True
    Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    Me.OrderByOn = True
    
    Set db = CurrentDb
    strSQL = "SELECT Order_Contents.Customer_Name, Order_Contents.Style_Name, Order_Contents.Due_Date," _
        & " Order_Contents.Ring_Size, Order_Contents.Scheduled_Date, Order_Contents.Comments," _
        & " Order_Contents.Rush, Order_Contents.Critical, Order_Contents.Remake, Order_Contents.Custom" _
        & " FROM Order_Contents" _
        & " WHERE (Order_Contents.Order_Num = """ & gstrOrderSearch & """)" _
        & " AND (Order_Contents.Ring_ID_No = """ & gstrRingSearch & """)"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    tbOrderRingDisp = gstrOrderSearch & "-" & gstrRingSearch
    tbCustomerName = rs("Customer_Name")
    tbStyleName = rs("Style_Name")
    tbDueDate = rs("Due_Date")
    tbRingSize = rs("Ring_Size")
    tbScheduledDate = rs("Scheduled_Date")
    tbComments = rs("Comments")
    cbRush = rs("Rush")
    cbCritical = rs("Critical")
    cbRemake = rs("Remake")
    cbCustom = rs("Custom")
        
    rs.Close
    db.Close
    Me.Refresh
    
    
TestSearch_Exit:
    Exit Sub


TestSearch_Err:
    MsgBox Error$
    Resume TestSearch_Exit
    
End Sub

Any ideas?

**pbaldy** · 08-25-2017, 05:06 PM

What are the data types of the 2 fields in the filter?

**ssanfu** · 08-25-2017, 05:10 PM

If you have unbound controls on a form, why are you trying to set a filter on the form record source?
Isn't the form unbound also?

More info?????

I modified your code a bit.....

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
    Me.Filter = "(Order_Num = '" & gstrOrderSearch & "') AND (Ring_ID_No = '" & gstrRingSearch & "')"    '<<-- changed the double quotes to single quotes
    Me.FilterOn = True
    Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    Me.OrderByOn = True
    
    Set db = CurrentDb
    strSQL = "SELECT Order_Contents.Customer_Name, Order_Contents.Style_Name, Order_Contents.Due_Date," _
        & " Order_Contents.Ring_Size, Order_Contents.Scheduled_Date, Order_Contents.Comments," _
        & " Order_Contents.Rush, Order_Contents.Critical, Order_Contents.Remake, Order_Contents.Custom" _
        & " FROM Order_Contents" _
        & " WHERE (Order_Contents.Order_Num = '" & gstrOrderSearch & "')" _   '<<-- changed the double quotes to single quotes
        & " AND (Order_Contents.Ring_ID_No = '" & gstrRingSearch & "')"  '<<-- changed the double quotes to single quotes
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' use the "Me." syntax to refer to controls on a form
    Me.tbOrderRingDisp = gstrOrderSearch & "-" & gstrRingSearch
    Me.tbCustomerName = rs("Customer_Name")
    Me.tbStyleName = rs("Style_Name")
    Me.tbDueDate = rs("Due_Date")
    Me.tbRingSize = rs("Ring_Size")
    Me.tbScheduledDate = rs("Scheduled_Date")
    Me.tbComments = rs("Comments")
    Me.cbRush = rs("Rush")
    Me.cbCritical = rs("Critical")
    Me.cbRemake = rs("Remake")
    Me.cbCustom = rs("Custom")
        
TestSearch_Exit:
    rs.Close
    '  db.Close   '<< you DO NOT "close" the database object. You didn't open it!
   Set rs = Nothing
   Set db = Nothing

    Me.Refresh   
    Exit Sub

TestSearch_Err:
    MsgBox Error$
    Resume TestSearch_Exit
    
End Sub

**Zellax** · 08-28-2017, 09:01 AM

I have changed the double double quotes into single quotes, nixed the 'db.Close', and put the 'Me.' prefix on as you recommended but it changes nothing so far.

The form will be a viewed as a split-form so I am using the form's recordset to populate the datasheet part. The form's control source grabs the production history for an item and the filter determines which item's history we are displaying. The text boxes (prefix 'tb') and check boxes ('cb') are populated manually because they pull data from a different source than the form's recordset.

The two fields in the filter are global strings ('gstr') because I don't really know how else to pass that information into the form. I need to be able to call this search form from any applicable form so I don't have a single consistent field to point it at. Therefore, the SearchFunc() function will be responsible for setting these two global strings when a search is made.

Can anyone explain to me what is going on with this OpenForm command?

Code:

DoCmd.OpenForm "RingSearchResult", acNormal, "", "", , acWindowNormal

Why does it not use parentheses? I've tried to put in parentheses so it looks like

Code:

DoCmd.OpenForm("RingSearchResult", acNormal, "", "", , acWindowNormal)

And it now expects an equal sign somewhere (where?!?!?). I don't understand the syntactical difference. I also don't understand the syntax when Access uses the period vs exclaimation point vs underscore but that is OT I suppose.

**Zellax** · 08-28-2017, 09:15 AM

Oh yeah, Order_Num and Ring_ID_No are both strings, not numbers. Humph! This whole database is full of shenanigans like that. I'd better not share what I think of the previous developers for fear they see it.

**ssanfu** · 08-28-2017, 04:26 PM

Soooooo, now it is time for troubleshooting..........

I don't (and never have) used split forms, so I'll be asking questions. (I would use a standard form and put the unbound controls in the form header or footer.)

What is the SQL of the form "RingSearchResult"?
Have to ask since I don't have your dB.... You're sure there are fields named "Order_Num" and "Ring_ID_No" in the record source?

If you comment out the Filter and Order lines, what happens? Does the form open? Error messages?

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
    '  Me.Filter = "(Order_Num = '" & gstrOrderSearch & "') AND (Ring_ID_No = '" & gstrRingSearch & "')"    '<<-- changed the double quotes to single quotes
    '  Me.FilterOn = True
    '  Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    '  Me.OrderByOn = True
    
    Set db = CurrentDb
<snip>

What happens (errors, messages) if you change the Me.Filter line to

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
     Me.Filter = "(Order_Num = '" & gstrOrderSearch & "')"

    '  Me.Filter = "(Order_Num = '" & gstrOrderSearch & "') AND (Ring_ID_No = '" & gstrRingSearch & "')"    '<<-- changed the double quotes to single quotes
    '  Me.FilterOn = True
    '  Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    '  Me.OrderByOn = True
    
    Set db = CurrentDb
<snip>

Next change: what happens (errors, messages) if you change the Me.Filter line to

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
     Me.Filter = "(Ring_ID_No = '" & gstrRingSearch & "')"

    '  Me.Filter = "(Order_Num = '" & gstrOrderSearch & "') AND  (Ring_ID_No = '" & gstrRingSearch & "')"    '<<-- changed  the double quotes to single quotes
    '  Me.FilterOn = True
    '  Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    '  Me.OrderByOn = True
    
    Set db = CurrentDb
<snip>

Now try:

Code:

Private Sub Form_Load()    
    On Error GoTo TestSearch_Err
    
    Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
    
    ' gstrOrderSearch and gstrRingSearch are globally defined
    '  Me.Filter = "(Order_Num = '" & gstrOrderSearch & "') AND  (Ring_ID_No = '" & gstrRingSearch & "')"    '<<-- changed  the double quotes to single quotes
    '  Me.FilterOn = True
    Me.OrderBy = "Scan_Time_Stamp" ' Scan_Time_Stamp in in the form's recordset
    Me.OrderByOn = True
    
    Set db = CurrentDb
<snip>

Then change the code

Code:

Public Function SearchFunc()    
    On Error GoTo TestSearch_Err
    
    ' hard coded only for testing
    gstrOrderSearch = "251822"
    gstrRingSearch = "1"
    
    '   DoCmd.OpenForm "RingSearchResult", acNormal, "", "", , acNormal

    DoCmd.OpenForm "RingSearchResult", , , "(Order_Num = '" & gstrOrderSearch & "')"    

TestSearch_Exit:
    Exit Function


TestSearch_Err:
    MsgBox Error$
    Resume TestSearch_Exit
    
End Function

Change the "Openform" line to

Code:

DoCmd.OpenForm "RingSearchResult", , , "(Ring_ID_No = '" & gstrRingSearch & "')"

If no errors, then then try both where clauses:

Code:

DoCmd.OpenForm "RingSearchResult", , , "(Order_Num = '" & gstrOrderSearch & "') AND (Ring_ID_No = '" & gstrRingSearch & "')"

**Zellax** · 08-28-2017, 04:52 PM

Solved (finally)
The biggest problem is that I mistakenly thought that Ring_ID_No was a string and I finally realized in is a number so I just took out the single quotes. It didn't like my

Code:

intName = '4'
as opposed to
intName = 4

syntax for some obvious reason.

This made it so it really didn't like the Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) statement because the strSQL was wrong.

**ssanfu** · 08-28-2017, 05:35 PM

Isn't debugging fun???

Glad you sorted it out..

Good luck with your project.

Stumped: Data type mismatch in criteria expression.

Thread Tools

Stumped: Data type mismatch in criteria expression.

Similar Threads

data type mismatch in criteria expression

Data Type Mismatch in Criteria Expression

Data Type Mismatch in Criteria Expression

Data type mismatch in criteria expression

Data type mismatch in criteria expression

Posting Permissions