WHERE Question

[dfenton21]

Hi All,

I have a simple question (hopefully).

SELECT
Employees.StaffID, 
Employees.StaffName, 
DATEDIFF('d', Employees.StaffStartDate, now()) \ 7 AS ServiceLength, 
Areas.AreaDescription, 

IIF((SELECT SessionDate FROM Sessions LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID
WHERE Bookings.StaffID = Employees.StaffID AND Sessions.SessionType = 'Selling Skills' AND Bookings.Attendence = 'Attended') <> Null, TRUE, FALSE) AS SellingSkillsAttendence, 

IIF((SELECT SessionDate FROM Sessions LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID
WHERE Bookings.StaffID = Employees.StaffID AND Sessions.SessionType = 'Manual Handling' AND Bookings.Attendence = 'Attended') <> Null, TRUE, FALSE) AS ManualHandlingAttendence, 

IIF((SELECT SessionDate FROM Sessions LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID
WHERE Bookings.StaffID = Employees.StaffID AND Sessions.SessionType = 'EPOS' AND Bookings.Attendence = 'Attended') <> Null, TRUE, FALSE) AS EPOSAttendence, 

IIF((SELECT SessionDate FROM Sessions LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID
WHERE Bookings.StaffID = Employees.StaffID AND (Sessions.SessionType = 'One Day Induction' OR Sessions.SessionType = 'Induction - Second Day' OR Sessions.SessionType = 'Induction - First Day') AND Bookings.Attendence = 'Attended') <> Null, TRUE, FALSE) AS InductionAttendence, 

IIF((ABS(SellingSkillsAttendence) + ABS(ManualHandlingAttendence) + ABS(EPOSAttendence) + ABS(InductionAttendence)) = 4, TRUE, FALSE) AS InductionProgrammeAttendence

FROM ((Employees LEFT JOIN Departments ON Employees.StaffDept = Departments.DeptID) LEFT JOIN Bookings ON Employees.StaffID = Bookings.StaffID) LEFT JOIN Areas ON Departments.DeptArea = Areas.ID

The if statement in the code above counts the number of TRUE values in the four fields. If all four fields are TRUE, it returns TRUE otherwise returns FALSE.

I want to be able to filter the recordset to only show the records where the result of the if statement is FALSE. I assumed this would work after the FROM clause:

WHERE InductionProgrammeAttendence = FALSE

But it is asking me to enter a parameter value for InductionProgrammeAttendence.



Any advice would be appreciated because it seems like a very simple task for Access to do.

If it makes a difference, I'm using 97

Thanks.

[pbaldy]

Because of the order in which the clauses are processed, you can't refer to an alias in the WHERE clause. Because of the number of calculations involved, I'd probably base a second query on this one and use the criteria you intended to use.