Data type mismatch??

[Les Isaacs]

Hi All

This works fine...

SELECT [qry Submissionsheets].[Employee Name], CDate(CDate([Sickness - Start Date])-Weekday(CDate([Sickness - Start Date]))+1*[tblCount].[CountID]) AS AbsenceDate, Val(DateDiff("d",[Sickness - Start Date],[Sickness - End Date])) AS Expr2
FROM tblCount, [qry Submissionsheets] INNER JOIN staffs ON [qry Submissionsheets].[Employee Name] = staffs.staff_name
WHERE (((CDate(CDate([Sickness - Start Date])-Weekday(CDate([Sickness - Start Date]))+1*[tblCount].[CountID])) Between CDate([Sickness - Start Date]) And CDate([Sickness - End Date])) AND ((IsDate([Sickness - Start Date]))=True) AND ((IsDate([Sickness - End Date]))=True))
ORDER BY [qry Submissionsheets].[Employee Name];

... but when I just add a criteria for Expr2 and rerun the query - on the same data - (so the query becomes as below) I get a Data type mismatch error!! I've used the IsDate function to check that I have dates where expected, and used Val on Expr2 to ensure that I have a numeric value, and when I run the query above, without the criteria, I get the correct results for Expr2. But with any criteria at all for Expr2 I get a Data type mismatch error.
Hope someone can help - this is driving me nuts.
The query that errors is:

SELECT [qry Submissionsheets].[Employee Name], CDate(CDate([Sickness - Start Date])-Weekday(CDate([Sickness - Start Date]))+1*[tblCount].[CountID]) AS AbsenceDate, Val(DateDiff("d",[Sickness - Start Date],[Sickness - End Date])) AS Expr2
FROM tblCount, [qry Submissionsheets] INNER JOIN staffs ON [qry Submissionsheets].[Employee Name] = staffs.staff_name
WHERE (((CDate(CDate([Sickness - Start Date])-Weekday(CDate([Sickness - Start Date]))+1*[tblCount].[CountID])) Between CDate([Sickness - Start Date]) And CDate([Sickness - End Date])) AND ((IsDate([Sickness - Start Date]))=True) AND ((IsDate([Sickness - End Date]))=True) AND ((Val(DateDiff("d",[Sickness - Start Date],[Sickness - End Date])))=3))
ORDER BY [qry Submissionsheets].[Employee Name];

Many thanks
Les

 

[Les Isaacs]

OK - bit of an update!

It would appear that there are a few very strange values in [Sickness - Start Date] and [Sickness - End Date] - by which I mean that, for the 'strange' records:

1. Looking at the raw data there appears to be nothing
2. Len([Sickness - Start Date]) returns a zero (whereas Len([Sickness - Start Date] return an empty string for most records with no [Sickness - Start Date] value)
3. IsNull([Sickness - Start Date] returns zero (i.e. False, not as expected, whereas IsNull([Sickness - Start Date] returns -1 for most records with no [Sickness - Start Date] value)
4. IsDate([Sickness - Start Date] returns zero (same as for other records with no [Sickness - Start Date] value)

The problem now is that setting the criteria of True for IsDate([Sickness - Start Date] is only excluding the 'strange' records if there's no criteria for the expression Val(DateDiff("d",[Sickness - Start Date],[Sickness - End Date])) - the query then runs fine, and excludes the 'strange' records; but when I add a criteria to the expression Val(DateDiff("d",[Sickness - Start Date],[Sickness - End Date])) I get the data type mismatch error!

Really hope someone can help - I've spent all morning on this now and don't know what to try next.

Many thanks
Les