By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Data Type mismatch

P: 14
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(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If


'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(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
Aug 3 '07 #1
Share this Question
Share on Google+
2 Replies


P: 9
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.




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(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If


'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(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
Aug 3 '07 #2

JConsulting
Expert 100+
P: 603
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(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If


'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(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
Aug 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.