Data Type mismatch

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • zoro25
    New Member
    • Apr 2007
    • 14
    #1

    Data Type mismatch

    Hi,

    I'm pretty sure it's a no brainer for some people, but I'm lost...

    I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.

    Here's the code:
    Private Sub cmdFilter_Click ()
    Dim varItem As Variant
    Dim strSite_Name As String
    Dim strYear As String
    Dim strFilter As String

    'For Site_Name'
    For Each varItem In Me.lstSite_Name .ItemsSelected
    strSite_Name = strSite_Name & ",'" & Me.lstSite_Name .ItemData(varIt em) & "'"
    Next varItem
    If Len(strSite_Nam e) = 0 Then
    strSite_Name = "Like '*'"
    Else
    strSite_Name = Right(strSite_N ame, Len(strSite_Nam e) - 1)
    strSite_Name = "IN (" & strSite_Name & ")"
    End If


    'For Year'
    For Each varItem In Me.lstYear.Item sSelected
    strYear = strYear & ",'" & Me.lstYear.Item Data(varItem) & "'"
    Next varItem
    If Len(strYear) = 0 Then
    strYear = "Like '*'"
    Else:
    strYear = Right(strYear, Len(strYear) - 1)
    strYear = "IN(" & strYear & ")"
    End If


    strFilter = "[Site_Name] " & strSite_Name & _
    "AND [Year] " & strYear

    With Reports![rpt_detail]
    .Filter = strFilter
    .FilterOn = True

    End With


    End Sub

    Anyone can help?

    Thank you

    Marie
    Tags: None
    • P3Eddie
      New Member
      • Aug 2007
      • 9
      #2
      Marie:

      I'm fairly new at all of this, but I had similar needs on a database of my own. I concocted an elaborate workaround using the DatePart() function, which I know lets you pull out a part of a date (like, d or m or yyyy) to use with/against date values. I don't know enough to find a way to work it into your code, but maybe it's a step in the right direction?

      Sorry if I'm way off base here.




      Originally posted by zoro25
      Hi,

      I'm pretty sure it's a no brainer for some people, but I'm lost...

      I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.

      Here's the code:
      Private Sub cmdFilter_Click ()
      Dim varItem As Variant
      Dim strSite_Name As String
      Dim strYear As String
      Dim strFilter As String

      'For Site_Name'
      For Each varItem In Me.lstSite_Name .ItemsSelected
      strSite_Name = strSite_Name & ",'" & Me.lstSite_Name .ItemData(varIt em) & "'"
      Next varItem
      If Len(strSite_Nam e) = 0 Then
      strSite_Name = "Like '*'"
      Else
      strSite_Name = Right(strSite_N ame, Len(strSite_Nam e) - 1)
      strSite_Name = "IN (" & strSite_Name & ")"
      End If


      'For Year'
      For Each varItem In Me.lstYear.Item sSelected
      strYear = strYear & ",'" & Me.lstYear.Item Data(varItem) & "'"
      Next varItem
      If Len(strYear) = 0 Then
      strYear = "Like '*'"
      Else:
      strYear = Right(strYear, Len(strYear) - 1)
      strYear = "IN(" & strYear & ")"
      End If


      strFilter = "[Site_Name] " & strSite_Name & _
      "AND [Year] " & strYear

      With Reports![rpt_detail]
      .Filter = strFilter
      .FilterOn = True

      End With


      End Sub

      Anyone can help?

      Thank you

      Marie

        Comment

        • JConsulting
          Recognized Expert Contributor
          • Apr 2007
          • 603
          #3
          Originally posted by zoro25
          Hi,

          I'm pretty sure it's a no brainer for some people, but I'm lost...

          I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.

          Here's the code:
          Private Sub cmdFilter_Click ()
          Dim varItem As Variant
          Dim strSite_Name As String
          Dim strYear As String
          Dim strFilter As String

          'For Site_Name'
          For Each varItem In Me.lstSite_Name .ItemsSelected
          strSite_Name = strSite_Name & ",'" & Me.lstSite_Name .ItemData(varIt em) & "'"
          Next varItem
          If Len(strSite_Nam e) = 0 Then
          strSite_Name = "Like '*'"
          Else
          strSite_Name = Right(strSite_N ame, Len(strSite_Nam e) - 1)
          strSite_Name = "IN (" & strSite_Name & ")"
          End If


          'For Year'
          For Each varItem In Me.lstYear.Item sSelected
          strYear = strYear & ",'" & Me.lstYear.Item Data(varItem) & "'"
          Next varItem
          If Len(strYear) = 0 Then
          strYear = "Like '*'"
          Else:
          strYear = Right(strYear, Len(strYear) - 1)
          strYear = "IN(" & strYear & ")"
          End If


          strFilter = "[Site_Name] " & strSite_Name & _
          "AND [Year] " & strYear

          With Reports![rpt_detail]
          .Filter = strFilter
          .FilterOn = True

          End With


          End Sub

          Anyone can help?

          Thank you

          Marie

          Syntax for an IN statement using dates looks like this

          In (#5/21/2007#,#6/1/2007#)

          so you just need to alter your string create code to include the pound signs.
          J

            Comment

            Previous template Next
            Working...