'Data type mismatch in criteria expression' - when filtering report (1 Viewer)

[captain1974]

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:

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

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:

' 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

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?

 

[RuralGuy]

Have you checked to make sure you do not have any Access Reference Problems on the 2nd machine?

 

[boblarson]

Well, I don't know how it is working on other computers when you have

Public Function Vintage(EDate As Date) As Integer

and then are trying to assign a string as value

Vintage = "1995"

when it should be an Integer

Vintage = 1995

 

[captain1974]

Well, I don't know how it is working on other computers when you have

Public Function Vintage(EDate As Date) As Integer

and then are trying to assign a string as value

Vintage = "1995"

when it should be an Integer

Vintage = 1995

Thanks Bob,
Not sure why I had an Integer as a String, I wrote that function a while ago. Correcting it hasn't made any difference to the error though.

 

[boblarson]

Which line is it first choking on?

 

[captain1974]

Have you checked to make sure you do not have any Access Reference Problems on the 2nd machine?

Thanks, I unfortunately can't follow that work-through as the original machine has been sent away for repair. I don't think I selected any unusual references though. (ADO 2.7 I know was selected on both machines)

 

[captain1974]

Which line is it first choking on?

It doesn't go into Debug, so I don't know. Actually after the error message it sometimes either performs the filter or closes the report.

 

[boblarson]

Is it possible that ADO 2.7 is not available on the particular machine. I typically will choose 2.5, just in case, so that if they don't have all of the service packs and MDAC updates, it won't be a problem.

 

[boblarson]

It doesn't go into Debug, so I don't know. Actually after the error message it sometimes either performs the filter or closes the report.

If it doesn't let you go to debug when the error message is displayed, it would appear that you have error handling in place and I would disable it temporarily so that it will kick back the errror message with the DEBUG button on there.

 

[captain1974]

I have selected 'Break on All Errors' but it is not going into Debug.

 

[boblarson]

Do you have an error handler on the code that calls the function?

 

[captain1974]

As far as I can see there are error handlers in the code, but not to handle the errors in question. The code was based on http://www.fontstuff.com/access/acctut19code5.htm
and I am still very much a learner with VBA. If posting the db will help then I can do so (what is the easiest way to remove records?)

 

[boblarson]

It doesn't have to be handling that particular error, but if you disable that error handling temporarily by putting a ' single quote to the left of it (commenting out) then you should get a dialog when the error occurs and it should have the debug button available. When you have error handling in an event which has an error, you don't get that button.

 

[captain1974]

It doesn't have to be handling that particular error, but if you disable that error handling temporarily by putting a ' single quote to the left of it (commenting out) then you should get a dialog when the error occurs and it should have the debug button available. When you have error handling in an event which has an error, you don't get that button.

I've tried commenting out the error handling with no success. You can download the db here (as is too big to post to this site):
http://www.ahaviticulture.com.au/rdp
If you are able to have a look.

 

[boblarson]

Which item do I need to select?

 

[boblarson]

Unfortunately I have to go to work now and I only have Access 2000 there. I'll probably have to look at it when I get home, if you haven't sorted it by then.

 

[captain1974]

From the switchboard menu, select Reports > Maturity Results. Otherwise fdlgMaturityFilter from the database window.

 

[RuralGuy]

You have a bad record in the tblMatSample table. Sort the MSampleDate ascending and you will see it.

 

[captain1974]

You have a bad record in the tblMatSample table. Sort the MSampleDate ascending and you will see it.

Thank you very much RuralGuy, deleting that record has solved the problem. Is there a way to automatically delete blank records such as these without going into the table to find them? I think this particular case came from a record being entered in the wrong place and then hitting backspace rather than selecting the record and deleting it. Users could be trained to delete the record rather than backspace, but is there a way for error handling on the form to pick this up?

 

[RuralGuy]

This type of error could be caught in a form in the BeforeUpdate event. That is where you would do final validity checks along with the BeforeUpdate event of each control for field validity checks.