'data type mismatch in criteria expression' after Access query

Question

I'm trying to count the total number of separate values in a query I've created in Access 2010 but I get an error message reading 'data type mismatch in criteria expression.' I can't see what that might be.

This is the query:

SELECT 
SUM(IIF(n.recall_method = "SMS",1 ,0)) AS SMS,
SUM(IIF(n.recall_method = "Letter",1 ,0)) AS Letter,
SUM(IIF(n.recall_method = "Practice invite",1 ,0)) AS [Practice Invite],
SUM(IIF(n.recall_method = "Advised to self-book",1 ,0)) AS [Advised to self-book],
SUM(IIF(n.recall_method is null,1 ,0)) AS [No Recall Method]

FROM  no_vaccine_or_care_home_vw AS n;

n.recall_method is ultimately pulling from a text field in a table but via a separate query, which itself relies on another query. In case the problem is arising here, here's the SQL for these:

no_vaccine_or_care_home_vw:

SELECT a.nhs_number, r.recall_method
FROM (all_patients_care_home_or_vacc_status_vw AS a 
LEFT JOIN patient_tbl AS p ON a.nhs_number=p.nhs_number) 
LEFT JOIN patient_recall_tbl AS r ON a.nhs_number=r.nhs_number
WHERE a.care_home is null 
AND a.vaccinated is null
AND age(p.date_of_birth)>17;

(age is a function calculating age from date of birth)

all_patients_care_home_or_vacc_status_vw:

SELECT n.nhs_number, 
 c.nhs_number AS care_home, 
 v.nhs_number AS vaccinated
FROM (nhs_no_tbl AS n 
LEFT JOIN patient_care_home_tbl AS c ON n.nhs_number = c.nhs_number) 
LEFT JOIN patient_vaccinated_tbl AS v ON n.nhs_number = v.nhs_number;

(Any object ending in _tbl is a table, unsurprisingly.)

Thanks for any help.

Does no_vaccine_or_care_home_vw run without errors? Did you double check the function age()? — forpas
@forpas Hmm. You're right that the problem is the function age(). I'll mark this as the accepted answer if you post it as an answer, thanks. I'm not sure why the function didn't work, however. I use it with that table and field regularly and it's never been a problem. — Zierspargel
It's fine, there is no need for an answer. Only if you find what the problem is in the function, post it as your answer. — forpas

Gustav Gustav · Accepted Answer · 2021-01-24T10:50:21

It will fail if you pass Null to the function, so prevent that, for example like this:

AND age(Nz(p.date_of_birth, Date()))>17;

'data type mismatch in criteria expression' after Access query

1 Answers