[SQL] Getting Data type mismatch in criteria expression when comparing DateDiff value to an...

I am querying a table to get the start and end dates of a probation, then calculate the number of months until the halfway point, the date of the halfway point, and how many months that halfway point is from the present.

This is from a Microsoft Access format mdb database file, and I'm using MDB Viewer Plus to view and operate on the data. That program performs just fine for performing queries.

Here is a query that works, followed by a screenshot of an output table with accurate values. The data types are either date or number in this particular view.

select ReceiptDate,SupervisionTerminationDate
, DateDiff("m",[ReceiptDate],[SupervisionTerminationDate]) as ProbationLength
, int(ProbationLength/2) as HalfProbationLength
, DateAdd("m",HalfProbationLength,[ReceiptDate]) as HalfProbationDate
, int(DateDiff("m",HalfProbationDate,NOW())) as HalfProbationMonthsTillNow
from list3
where len([SupervisionTerminationDate]) > 0 and len([ReceiptDate]) > 0
and Year([SupervisionTerminationDate]) > 2022




But now I need to find out how many months back from Now() or from Now() until the probation halfway point. I would use the aliases I set up for the col names, like "HalfProbationMonthsTillNow" as integers in a DateDiff() to simply add to or subtract from Now(), but apparently MS Access doesn't allow variables to be declared, so I have to spell out the entire calculation, like so (it's the last line of the sql):

select ReceiptDate,SupervisionTerminationDate
, DateDiff("m",[ReceiptDate],[SupervisionTerminationDate]) as ProbationLength
, int(ProbationLength/2) as HalfProbationLength
, DateAdd("m",HalfProbationLength,[ReceiptDate]) as HalfProbationDate
, int(DateDiff("m",HalfProbationDate,NOW())) as HalfProbationMonthsTillNow
from list3
where len([SupervisionTerminationDate]) > 0
and len([ReceiptDate]) > 0
and Year([SupervisionTerminationDate]) > 2022
and DateDiff("m",DateAdd("m",DateDiff("m",[ReceiptDate],[SupervisionTerminationDate])/2,[ReceiptDate]),NOW()) < 9


...but when I run the query with that last line present, it throws a "Data type mismatch in criteria expression" error:



If I take away the comparator part of the last WHEN condition, and place it in one of the field lines, it outputs just fine. It looks like a proper integer. But when I compare it to an integer, like > 9 it throws the error.

Continue reading...