captain1974
Registered User.
- Local time
- Tomorrow, 05:42
- Joined
- Jan 15, 2007
- Messages
- 43
I have set up a report filter based on the example by Martin Green:
http://www.fontstuff.com/access/acctut19.htm
This was all working very nicely indeed, without any errors.
However, I have transferred the database from one pc to another (using an external drive) and I am now receiving the error message 'Data type mismatch in criteria expression' when trying to filter.
Both PCs are using the same OS and version of Office (2003).
I suspect it is something to do with one of the filter criteria being based on a date, though how Access picks and chooses when or not to tell me there is an error is beyond me.
A bit more about what I'm trying to do:
I wrote a simple module to extract the "year" from a date that falls within a particular range:
A query then calculates the year/vintage of dates for particular activities using the expression: Vintage: Vintage([MSampleDate])
This works fine in the query, and the report is based on the query.
I am thinking that when the filter is trying to filter based on eg "2007" the reference is going back to the previous query/function and the syntax of the code is not right for choosing a date.
The relevant bit of the filter code is:
My filter box looks a bit like Mr Green's, except the list box on the right contains a list of years.
Can anyone help with my syntax and/or why Access does this when changing machines?
http://www.fontstuff.com/access/acctut19.htm
This was all working very nicely indeed, without any errors.
However, I have transferred the database from one pc to another (using an external drive) and I am now receiving the error message 'Data type mismatch in criteria expression' when trying to filter.
Both PCs are using the same OS and version of Office (2003).
I suspect it is something to do with one of the filter criteria being based on a date, though how Access picks and chooses when or not to tell me there is an error is beyond me.
A bit more about what I'm trying to do:
I wrote a simple module to extract the "year" from a date that falls within a particular range:
Code:
Public Function Vintage(EDate As Date) As Integer
Select Case EDate
Case Is < 0
Vintage = "-Error"
Case Is < #6/1/1995#
Vintage = "1995"
.......
Case Is < #6/1/2011#
Vintage = "2011"
Case Is < #6/1/2012#
Vintage = "2012"
Case Is = Null
Vintage = ""
Case Else
Vintage = "Error"
End Select
End Function
This works fine in the query, and the report is based on the query.
I am thinking that when the filter is trying to filter based on eg "2007" the reference is going back to the previous query/function and the syntax of the code is not right for choosing a date.
The relevant bit of the filter code is:
Code:
' Build criteria string from lstYear listbox
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
' Build filter string
strFilter = "[VineyardName] " & strVineyard & _
" AND [Vintage] " & strYear
Can anyone help with my syntax and/or why Access does this when changing machines?