IIf Statement in Query Criteria

[AccessIM]

I have the following code in a query:

SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN,
qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE,
qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT,
qryPointsWithDropOffDates.POINTVALUE
FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON
qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between
[qryDisciplineNoticesReprint.CALCDATE] And
[qryDisciplineNoticesReprint.LASTINCIDENTDATE]) AND
((qryPointsWithDropOffDates.POINTVALUE]<>0))
ORDER BY qryPointsWithDropOffDates.EMPLOYEEID,
qryPointsWithDropOffDates.INCIDENTDATE;

This works fine in most circumstances. However, I did fine a loop hole I
need to fix. If the field
[qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", I
need to change the between statement to read "Between
[qryDisciplineNoticesReprint.CALCDATE] And
[qryDisciplineNoticesReprint.DISCIPLINEDATE]).

The code I am trying looks like this:

SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN,
qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE,
qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT,
qryPointsWithDropOffDates.POINTVALUE
FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON
qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
WHERE
(((qryPointsWithDropOffDates.INCIDENTDATE)=IIf([qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE
INFORMATION FORM", (qryPointsWithDropOffDates.INCIDENTDATE] Between
[qryDisciplineNoticesReprint.CALCDATE] And
[qryDisciplineNoticesReprint.DISCIPLINEDATE]),(qryPointsWithDropOffDates.INCIDENTDATE)
Between [qryDisciplineNoticesReprint.CALCDATE] And
[qryDisciplineNoticesReprint.LASTINCIDENTDATE])) AND
((qryPointsWithDropOffDates.POINTVALUE]<>0))
ORDER BY qryPointsWithDropOffDates.EMPLOYEEID,
qryPointsWithDropOffDates.INCIDENTDATE;

With the added IIf statement in the criteria, the query returns no records.
It should return 9 records.

Can someone tell me what I am doing wrong int he code above? Thank you in
advance.

 

[Duane Hookom]

You can't put the Betwen inside the IIf(). Try a WHERE CLAUSE of
WHERE INCIDENTDATE Between CALCDATE And
IIF(DISCIPLINE]="ATTENDANCE INFORMATION FORM",
DISCIPLINEDATE,LASTINCIDENTDATE)
AND POINTVALUE<>0