Share via

Run-time error 13: Type mismatch

Anonymous
2019-02-15T06:26:19+00:00

Hi All,

My date range code having "Run-time error 13: Type mismatch"

Not sure what I have done wrong!

When I debug, it pointed and highlighted at below sentence in yellow (which is below font in red)

Sub Search()

Dim strFilter As String

If Not IsNull(Me.OpenFrom) Then

        strFilter = " AND OpenedDate >= #" & Format(Me.OpenFrom, "mm/dd/yyyy") & "#"

    End If

    If Not IsNull(Me.OpenTo) Then

        strFilter = strFilter & " AND OpenedDate <= #" & Format(DateAdd("d", 1, Me.OpenTo), "mm/dd/yyyy") & "#"

        End If

     strFilter = strFilter & " AND (Country='zzz'"

    If Nz(Me.chkSIN, False) Then

        strFilter = strFilter & " OR Country='Singapore'"

    End If

    If Nz(Me.chkMAL, False) Then

        strFilter = strFilter & " OR Country='Malaysia'"

    End If

    If Nz(Me.chkIDN, False) Then

        strFilter = strFilter & " OR Country='Indonesia'"

    End If

     If Nz(Me.chkPHI, False) Then

        strFilter = strFilter & " OR Country='Philippines'"

    End If

    strFilter = strFilter & ")"

    strFilter = strFilter & " AND (Status='zzz'"

    If Nz(Me.chkOpen, False) Then

        strFilter = strFilter & " OR Status='Open'"

    End If

    If Nz(Me.chkPending, False) Then

        strFilter = strFilter & " OR Status='Pending'"

    End If

    If Nz(Me.chkResolved, False) Then

        strFilter = strFilter & " OR Status='Resolved'"

    End If

    strFilter = strFilter & ")"

 If strFilter = "" Then

        Me.Filter = ""

        Me.FilterOn = False

        Me.Total = FindRecordCount("SELECT * FROM Issues")

    Else

        ' Remove first " AND "

        Me.Filter = Mid(strFilter, 6)

        Me.FilterOn = True

        Me.Total = FindRecordCount("SELECT * FROM Issues WHERE " & Me.Filter)

  End If

    End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2019-02-15T18:54:52+00:00

Try converting the OpenTo value to a date/time value with the CDate function:

strFilter = strFilter & " AND OpenedDate <= #" & Format(DateAdd("d", 1, CDate(Me.OpenTo)), "mm/dd/yyyy") & "#"

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2019-02-15T18:41:27+00:00

Second, if OpenTo is a Date/Time datatype, there is no need for the Format function which turns it into a string.

There is in the UK or any other country which does not use the US date format.  Without formatting the date literal in US format or an internationally unambiguous format such as the ISO standard of YYYY-MM-DD, the code would fail in those locations.  4th July would become 7th April!

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-02-17T04:49:28+00:00

    like nz(),I prefer initialize the string before concatenent.

    Sub Search()

    Dim strFilter As String

    strfilter=""

    If Not IsNull(Me.OpenFrom) Then

            strFilter = " AND OpenedDate >= #" & Format(Me.OpenFrom, "mm/dd/yyyy") & "#"

        End If

        If Not IsNull(Me.OpenTo) Then

            strFilter = strFilter & " AND OpenedDate <= #" & Format(DateAdd("d", 1, Me.OpenTo), "mm/dd/yyyy") & "#"

            End If

         strFilter = strFilter & " AND (Country='zzz'"

        If Nz(Me.chkSIN, False) Then

            strFilter = strFilter & " OR Country='Singapore'"

        End If

        If Nz(Me.chkMAL, False) Then

            strFilter = strFilter & " OR Country='Malaysia'"

        End If

        If Nz(Me.chkIDN, False) Then

            strFilter = strFilter & " OR Country='Indonesia'"

        End If

         If Nz(Me.chkPHI, False) Then

            strFilter = strFilter & " OR Country='Philippines'"

        End If

        strFilter = strFilter & ")"

        strFilter = strFilter & " AND (Status='zzz'"

        If Nz(Me.chkOpen, False) Then

            strFilter = strFilter & " OR Status='Open'"

        End If

        If Nz(Me.chkPending, False) Then

            strFilter = strFilter & " OR Status='Pending'"

        End If

        If Nz(Me.chkResolved, False) Then

            strFilter = strFilter & " OR Status='Resolved'"

        End If

        strFilter = strFilter & ")"

     If strFilter = "" Then

            Me.Filter = ""

            Me.FilterOn = False

            Me.Total = FindRecordCount("SELECT * FROM Issues")

        Else

            ' Remove first " AND "

            Me.Filter = Mid(strFilter, 6)

            Me.FilterOn = True

            Me.Total = FindRecordCount("SELECT * FROM Issues WHERE " & Me.Filter)

      End If

        End Sub

    Was this answer helpful?

    0 comments
  2. ScottGem 68,830 Reputation points Volunteer Moderator
    2019-02-15T13:01:38+00:00

    I see a couple of problems here. First, every addition to strFilter starts with an AND or OR.  But no where do you initialize strFilter. So the assumption is that strFilter is already starting with some value. is that correct? Oh, I see, the remove first AND code at the bottom, but that doesn't work. 

    Second, if OpenTo is a Date/Time datatype, there is no need for the Format function which turns it into a string.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-02-15T12:10:02+00:00

    I prefer using Nz in this situation:

    If Nz(Me.OpenTo, "") <> "" Then

    Was this answer helpful?

    0 comments