Using AND in a nested IIF statement comparing dates

[Matilda]

Dear Experts,

I get a "data type mismatch" error when I run this. I have checked the data
types, and am wonering if the comparison operators or the AND syntax is
wrong. Can you help please?

SELECT DISTINCT tblSubmissions.Campus, Max(tblSubmissions.SubmNo) AS
MaxOfSubmNo, IIf(([SubmExtrctEndDt]>=[PeriodEndDt1]) And
([SubmExtrctEndDt]<=[SubDL1]),1,IIf(([SubmExtrctEndDt]>=[PeriodEndDt2]) And
([SubmExtrctEndDt]<=[SubDL2]),2,"")) AS RptPeriod,
Max(tblSubmissions.SubmExtrctEndDt) AS MaxOfSubmExtrctEndDt
FROM tblSubmissions INNER JOIN tblESISDeadlineLU ON tblSubmissions.CalMnth =
tblESISDeadlineLU.CalMnthNo
GROUP BY tblSubmissions.Campus, IIf(([SubmExtrctEndDt]>=[PeriodEndDt1])and
([SubmExtrctEndDt]<=[SubDL1]),1,IIf(([SubmExtrctEndDt]>=[PeriodEndDt2]) And
([SubmExtrctEndDt]<=[SubDL2]),2,""))
ORDER BY tblSubmissions.Campus, Max(tblSubmissions.SubmNo);

Many TIAs

Matilda

 

[Allen Browne]

The obvious one here is RptPeriod.
Your nested IIf()s can return 3 values: 1, 2 or a zero-length string (zls.)
A ZLS is not a number, so its type doesn't match.

Try Null instead of the "".

IIf(([SubmExtrctEndDt]>=[PeriodEndDt1]) And ([SubmExtrctEndDt]<=[SubDL1]),
1,
IIf(([SubmExtrctEndDt]>=[PeriodEndDt2]) And
([SubmExtrctEndDt]<=[SubDL2]),2,
Null)) AS RptPeriod,

 

[Matilda]

Brilliant! Thanks, Allen