Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: Data Type Mismatch in Criteria Expression

    Hi all, Access 2000. Newest Puzzle. I am using a tutorial from Access Tips: Access and SQL Part 6: Dynamic Reports to make a form that uses criteria to print reports. I have managed to understand how this works and to modify it for my use. Except for an error that (I think) is in the Dim statements. I have added several new fields to the tutorial's version. The original has only text fields. These are my Dim statements:

    Code:
    Private Sub cmdApplyFilter_Click()
    ' Set Variables
        Dim strDate As String 'date field
        Dim strLocation As String 'text field
        Dim strCategory As String 'text field
        Dim strItem As String 'text field
        Dim strLevel As String 'text field
        Dim str800Inv As String 'y/n field (setting to Boolean does not seem to help)
        Dim strFilter As String
        Dim strDocName As String 'report
    
        strDocName = "rptA211Inventory" 'report name
    
    I have tried using:
    Dim strDate As String 'date field
    but then I get an error in this section (cbo box): 
    ' Build criteria string for Date field
        If IsNull(Me.cboDate.Value) Then
            strDate = "Like '*'"
        Else
            strDate = "='" & Me.cboDate.Value & "'" 'an error on this line
        End If
    Put it back to String and I get:
    "Data Type Mismatch in Criteria Expression"

    Anyone have any thoughts on what I am doing wrong. Let me know if I have supplied enough info. Many thanks. Scott
    Last edited by gvee; 11-06-13 at 11:39. Reason: Yello

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks valid to me, when you say this line reports an error do you actually means this line throws a wobbler or when you try to use strdate?

    the most likely reason is that cbodate is either invalid or holds an invalid value
    when in debug mode what result do you get if you type ?Me.cboDate in the immediate window

    BTW as MikeTheBike said back here
    dates MUST be delimted by using the hash symbol
    eg
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    Data Type Mismatch in Criteria Expression

    Ok, so if I use the date field, I get a dialog box that says there is a data type mismatch and when I click yes the report closes and I can go on to work again (no MS VB screen). So I have commented out all the date parts. Also, there is a y/n field named "800inv" in an option group. I have it set as boolean in the criteria section. With this not commented out, when I click "Apply Filter" I get the VB Runtime Error 13 type mismatch error dialog box. I click debug and it highlights the value selected in the option group on the form in the code. So obviously I need to either change the criteria or delimit it somehow or both.

    Sorry, I am having trouble describing this correctly. I know it must be hard to figure these thing out long distance.

    My question is how to delimit the date field correctly and what to do to the y/n field (delimit and/or change criteria). Everything else on the form and works and the report changes as the filters are applied.

    Below I have again included the criteria lines and the two codes sections for the two controls and the where clause line. Let me know if you want me post the whole thing. (The strFilter line is duplicated because the "date" version is commented out.) Thanks again, Scott

    Code:
    ' Build criteria string for Date field
    '    If IsNull(Me.cboDate.Value) Then
     '       strDate = "Like '*'"
      '  Else
       '     strDate = "='" & Me.cboDate.Value & "'"
        'End If
    Code:
    ' Build criteria string for 800 Inventory field
        Select Case Me.fra800inv.Value
            Case 1
                str800Inv = "='800'"
            Case 2
                str800Inv = "='Non 800'"
            Case 3
                str800Inv = "Like '*'"
        End Select
    Code:
    ' Combine criteria strings into a WHERE clause for the filter
        'strFilter = " [date] " & strDate & " AND  [item] " & strItem & " AND [location] " & strLocation & " AND [category] " & strCategory & " AND [level] " & strLevel
        strFilter = " [item] " & strItem & " AND [location] " & strLocation & " AND [category] " & strCategory & " AND [level] " & strLevel & " AND [800inv] " & str800Inv

  4. #4
    Join Date
    Mar 2013
    Posts
    70

    Data Type Mismatch in Criteria Expression

    Sorry forgot criteria section:

    Code:
    Private Sub cmdApplyFilter_Click()
    '    Dim strDate As String
        Dim strItem As String
        Dim strLocation As String
        Dim strCategory As String
        Dim strLevel As String
        Dim str800Inv As Boolean
        Dim strFilter As String

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As said in post #2:-
    BTW as MikeTheBike said back here
    dates MUST be delimted by using the hash symbol
    eg:-
    where mydatecolumn = #10/30/2013#

    delimit text literals with ' or "
    eg:-
    where mynumericcolumn = 1234 and mydatecolumn = #10/30/2013# and mytextcolumn = "inserttexthere"
    or
    where mynumericcolumn = 1234 and mydatecolumn = #10/30/2013# and mytextcolumn = 'inserttexthere'
    Last edited by healdem; 11-07-13 at 11:11.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    date is a reserved word that may well cause problems
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2013
    Posts
    70

    Data Type Mismatch in Criteria Expression

    Hi, Thanks for the reserved word reminder. I changed the field name to invDate.

    I don't like to be dense, but I do not know where to apply the hash tag/delimiters for the date fields in this instance. I have tried putting them in all the permutations I can think of. In some locations the code turns red and gives me a compile error. In others it compiles and then when I run the filter I get a VB error 13 type mismatch a type mismatch and it takes me to this line of code which makes sense but I do not know how to fix it. The code below has one of my permutations of hash tags. No matter where I put them I get the same result if I do not get red code.

    Code:
    strInvDate = "#='#" & Me.cboInvDate.Value & "#'#"
    the above is part of this code:
    Code:
        If IsNull(Me.cboInvDate.Value) Then
            strInvDate = "#Like '*'#"
        Else
            strInvDate = "#='#" & Me.cboInvDate.Value & "#'#"
        End If
    Here is the entire button's code in case it helps: Thanks, Scott
    Code:
    Private Sub cmdApplyFilter_Click()
        Dim strInvDate As Date
        Dim strItem As String
        Dim strLocation As String
        Dim strCategory As String
        Dim strLevel As String
    '    Dim str800Inv As Boolean 'y/n field not working yet
        Dim strFilter As String
    ' Check that the report is open and open if it is closed
        If SysCmd(acSysCmdGetObjectState, acReport, "rptA211Inventory") <> acObjStateOpen Then
            DoCmd.OpenReport "rptA211Inventory", acViewPreview
            Exit Sub
        End If
    ' Build criteria string for Date field
        If IsNull(Me.cboInvDate.Value) Then
            strInvDate = "#Like '*'#"
        Else
            strInvDate = "#='#" & Me.cboInvDate.Value & "#'#"
        End If
    ' Build criteria string for Item field
        If IsNull(Me.cboItem.Value) Then
            strItem = "Like '*'"
        Else
            strItem = "='" & Me.cboItem.Value & "'"
        End If
    ' Build criteria string for Location field
        If IsNull(Me.cboLocation.Value) Then
            strLocation = "Like '*'"
        Else
            strLocation = "='" & Me.cboLocation.Value & "'"
        End If
    ' Build criteria string for Category field
        If IsNull(Me.cboCategory.Value) Then
            strCategory = "Like '*'"
        Else
            strCategory = "='" & Me.cboCategory.Value & "'"
        End If
    ' Build criteria string for Level field
        Select Case Me.fraLevel.Value
            Case 1
                strLevel = "='BLS'"
            Case 2
                strLevel = "='ALS'"
            Case 3
                strLevel = "Like '*'"
        End Select
    ' Build criteria string for 800 Inventory field (y/n field not working yet)
    '    Select Case Me.fra800inv.Value
     '       Case 1
      '          str800Inv = "='800'"
       '     Case 2
        '        str800Inv = "='Non 800'"
         '   Case 3
          '      str800Inv = "Like '*'"
        'End Select
    ' Combine criteria strings into a WHERE clause for the filter
        strFilter = " [invDate] " & strInvDate & " AND [item] " & strItem & " AND [location] " & strLocation & " AND [category] " & strCategory & " AND [level] " & strLevel
    ' Apply the filter and switch it on
        With Reports![rptA211Inventory]
            .Filter = strFilter
            .FilterOn = True
        End With
    End Sub

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are making the filter harder that it need be
    you don't need to specify 'any row' using wildcards


    Code:
      strfitler = ""
        If not IsNull(Me.cboInvDate.Value) Then
            strfitler = strfilter  & " and InvDate = #" & format(Me.cboInvDate.Value,"mm/dd/yyyy") & "#"
        End If
    ' Build criteria string for Item field
        If not IsNull(Me.cboItem.Value) Then
            strfilter = strfilter & " and Item = '" & Me.cboItem.Value & "'"
        End If
    ' Build criteria string for Location field
        If not IsNull(Me.cboLocation.Value) Then
            strfilter = strfilter & " and Location = ' & Me.cboLocation.Value & "'"
        End If
    ' Build criteria string for Category field
        If not IsNull(Me.cboCategory.Value) Then
            strfilter = strfilter & " and Category = '" & Me.cboCategory.Value & "'"
        End If
    ' Build criteria string for Level field
        Select Case Me.fraLevel.Value
            Case 1
                strfilter = strfilter & " and Level = 'BLS'"
            Case 2
                strfilter = strfilter & " and Level = 'ALS'"
        End Select
    'chop off the first 5 characters
    strfilter = mid(strfitler,6)
    ' Apply the filter and switch it on
        With Reports![rptA211Inventory]
            .Filter = strFilter
            .FilterOn = True
        End With
    End Sub
    as ever its air code,m untested, unproven un anything... you wioll almost certainly need to debug it to make it work
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2013
    Posts
    70

    Data type mismatch in criteria expression

    Hello again,

    I have gone through your code and fixed a few typos and studied it. It seems very elegant. Does the strFilter= add each field and concatenate them as it goes?

    I have looked a my data structure and I have read alot about yes no fields and problems with them and access. So I took the 800 inventory y/n and created a new table (tbl800InvLU - field 1 = 800InvID autonumber and field 2 = Part800Inv text) to hold the values, fixed the relationships and a few forms and sql and added a new line to the code for the new cbo on the filter form.

    So the filter form works fine if I either 1.) have the Part800Inv section commented out or 2.) do not use that cbo on the form. As soon as I use it I get no data and #error in the date field on the report. Only the date field. The rest of the form is blank.

    I have put the new code below in case it helps. I do not think I know enough to find the problem. I am cross eyed from studying it. I cannot thank you enough for your patience.

    Best, Scott

    Code:
    Private Sub cmdApplyFilter_Click()
        Dim strInvDate As Date
        Dim strItem As String
        Dim strLocation As String
        Dim strCategory As String
        Dim strPart800Inv As String
        Dim strLevel As String
        Dim strFilter As String
    ' Check that the report is open and open if it is closed
        If SysCmd(acSysCmdGetObjectState, acReport, "rptA211Inventory") <> acObjStateOpen Then
            DoCmd.OpenReport "rptA211Inventory", acViewPreview
            Exit Sub
        End If
      strFilter = ""
    ' Build criteria string for Date field
        If Not IsNull(Me.cboInvDate.Value) Then
            strFilter = strFilter & " AND invDate = #" & Format(Me.cboInvDate.Value, "mmmm yyyy") & "#"
        End If
    ' Build criteria string for Item field
        If Not IsNull(Me.cboItem.Value) Then
            strFilter = strFilter & " AND item = '" & Me.cboItem.Value & "'"
        End If
    ' Build criteria string for Location field
        If Not IsNull(Me.cboLocation.Value) Then
            strFilter = strFilter & " AND location = '" & Me.cboLocation.Value & "'"
        End If
    ' Build criteria string for Category field
        If Not IsNull(Me.cboCategory.Value) Then
            strFilter = strFilter & " AND category = '" & Me.cboCategory.Value & "'"
        End If
    ' Build criteria string for 800 Inventory field
        If Not IsNull(Me.cboPart800Inv.Value) Then
           strFilter = strFilter & " AND part800Inv = '" & Me.cboPart800Inv.Value & "'"
        End If
    ' Build criteria string for Level field
        Select Case Me.fraLevel.Value
            Case 1
                strFilter = strFilter & " AND Level = 'BLS'"
            Case 2
                strFilter = strFilter & " AND Level = 'ALS'"
        End Select
    ' Build the SQL Filter
        If Not strFilter = "" Then
           strFilter = Mid(strFilter, 6)
        End If
    ' Apply the filter and switch it on
        With Reports![rptA211Inventory]
            .Filter = strFilter
            .FilterOn = True
        End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •