Error 3464 Data type mismatch in criteria expression

[LittleSmudge]

I have recently been updating a database I developed just over a year ago.

In BOTH there is the following sub:-

Private Sub ViewYoungsters()
Dim strSQL As String
Dim cqd As clsQueryDefs
Set cqd = New clsQueryDefs
strSQL = "SELECT CName, CRef, LastName, FirstName, UserName, DoB, " _
       & "CourseRef, SUId, EnrolmentDate, ContactMethod " _
       & "FROM tblC " _
       & "INNER JOIN (tblPerson " _
       & "INNER JOIN tblPersonCourse " _
       & "ON tblPerson.PersonId = tblPersonCourse.PersonRef) " _
       & "ON tblC.CId = tblPersonCourse.CRef " _
       & "WHERE (((DateAdd('yyyy',19,[DoB])) > DateSerial(Year([EnrolmentDate]),8,31))) "

If Len(Nz(cboCYL, "")) > 0 Then
    strSQL = strSQL & "AND CRef = '" & cboCYL & "' "
End If
strSQL = strSQL & "ORDER BY CRef, LastName, FirstName, DoB "

Call cqd.Reports(strSQL)
Set cqd = Nothing

DoCmd.OpenReport "rptYoungsters", acViewPreview
End Sub

In the original version this code works fine.
But all of a sudden in the latest version I get the
"Error 3464 Data type mismatch in criteria expression"
error.

If I take the SQL string and put it in a query of its own it fails too.
By experimentation I have determined that it is the WHERE caluse
WHERE (((DateAdd('yyyy',19,[DoB])) > DateSerial(Year([EnrolmentDate]),8,31)))
that it is objecting too.

What's more, something very strange happens in a query.
When I go to look at the data it first displays the data, then the message box appears with the error message in it.
When I lick on the Okay button all of the fields in all of the records turn to "#Name?"
BUT it still shows #Name? for the right number of records that the WHERE clause should select.
So the Where clause is doing the right calculation despite the error message.

What's going on ?


PS. the Where clause selects records where learner was <19 years old on 31August in year of course enrolment

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

data type mismatch is usually an indicator that you've passed in a string and it's expecting a number (or the opposite). Is DoB a Date? What about EnrolmentDate?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Probably because DateAdd is a VBA function that is being called from SQL and not a native SQL construct. The way to specify text strings to a VBA function call is to enclose them in double (not single) quotes. The single quotes are pretty much exclusive to SQL internal syntax. Try something like

& "WHERE (((DateAdd(""yyyy"",19,[DoB])) > DateSerial(Year([EnrolmentDate]),8,31))) "

 

[Golom]

Oh ... and the other thing is that DateAdd accepts "w", "d", "m", "y", "h", "n" or "s" as its first argument. "yyyy" isn't a valid first argument.

 

[PHV]

"yyyy" isn't a valid first argument
I thought it was the only valid argument to add years ...
"y" is for adding days ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittleSmudge]

Thanks for your inputs people.

Whilst I believe PHV is correct in that "yyyy" is the correct format - I got rid of DateAdd all together by converting it to

WHERE ([DoB] > DateSerial(Year([EnrolmentDate])-19,8,31))

That did not help at all, so I wrapped each side in CInt( ) converters.

THAT then told me what the real problem was.

One of the students has managed to get a Null value in their DoB.

Final solution then became

WHERE Is Not Null(DoB)
AND Is Not Null(EnrolmentDate)
AND ([DoB] > DateSerial(Year([EnrolmentDate])-19,8,31))



Thanks for all your help folks.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

WHERE Is Not Null(DoB)
Really ?
I'd use either:
WHERE Not IsNull(DoB)
or:
WHERE DoB Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886