data type mismatch error on date/time value (access 2003)

Topic

New Reply

When I run this SQL I’m getting a Data type mismatch error. I’m at a lose as to why. The table adminsum stores the appointment date as a text value. I’m trying to convert this to a date/time value in my query. Any thoughts why I’m getting this error? When I remove the group by on the admin date field the error goes away but I want to group by the different dates. Thanks..

SELECT AdminSum.[Admin ID], Count(AdminSum.[CBSR Appointment ID]) AS [CountOfCBSR Appointment ID], AdminSum.[Site ID], CLng(Nz([Appointment Date])) AS AdminDate
FROM AdminSum
WHERE (((AdminSum.[Event Outcome]) Not Like “Candidate No-Show”))
GROUP BY AdminSum.[Admin ID], AdminSum.[Site ID], CLng(Nz([Appointment Date]))
HAVING (((AdminSum.[Admin ID]) Not Like “2007*”));

Reply | Quote

Replies

What type of field is [Admin ID] ? Is it a Text field? Using Like with an ID field looks odd, but may be ok.

Does CLng work with your Appointment Dates?
Why don’t you use CDate?

Reply | Quote

What happens if you change both occurrences of

CLng(Nz([Appointment Date]))

to

CDate(Nz([Appointment Date],#01/01/1900#))

You can replace #01/01/1900# with whatever date you would want to use instead of a blank.

Reply | Quote

Tried it but I still get the same error. It runs and then fails. It’s only when I do a group by statement or add any kind of parameter. If I don’t group by any feilds or andd amy parameters it runs and converts the format to a date/time value. I set the properties of the field in the query to date/time value..

SELECT AdminSum.[Admin ID], Count(AdminSum.[CBSR Appointment ID]) AS [CountOfCBSR Appointment ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#)) AS AdminDate
FROM AdminSum
WHERE (((AdminSum.[Event Outcome]) Not Like “Candidate No-Show”))
GROUP BY AdminSum.[Admin ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#));

Reply | Quote

Does the Appointment Date field contain values that cannot be converted to a date? Create a ‘normal’ query that adds the AdminDate field, and look for records that show #Error or similar in the AdminDate column.

Reply | Quote

That was it Hans. I had some goofy dates in there. Once I got them out it ran ok..

Reply | Quote

Why are you using CLng to convert [Appointment Date] to a Date field? Use CDate()!

Reply | Quote