Filter Function Results

[Joel Marion]

Access 2003
I have a series of IIF/Select statements providing true/false analysis on a number of criteria (see code below), basically checking each table entry for specific criteria and spitting out text (ie: "Check This, It's Not Right") for my report. Once I've checked all these criteria, I want to filter my report to only show me data that doesn't meet at least one of the five criteria.

The data is coming from a linked, imported table, and I'm not able to place validation criteria on data entry (partly because some of the 'errors' this report identifies are actually acceptable anomalies).

I'm starting from this:

SELECT Qcv.Title, Qcv.Course, Qcv.Sec, Qcv.Location, Qcv.Room, Qcv.Day, Qcv.Start, Qcv.End, Qcv.Start1, Qcv.End1, IIf([Start1] In (select StandardDate from Dates),"","Check Start Date") AS StartDateCheck, IIf([End1] In (select StandardDate from Dates),"","Check End Date") AS EndDateCheck, IIf([Start] In (select SlotStart from Slots),"","Check Slot Start Time") AS SlotStartCheck, IIf([End] In (select SlotEnd from Slots),"","Check Slot End Time") AS SlotEndCheck, IIf([Location]="MAIN",IIf([Room] Is Null,"Loc. MAIN No Room",""),"") AS LocationCheck, IIf([StartDateCheck]="",IIf([EndDateCheck]="",IIf([SlotStartCheck]="",IIf([SlotEndCheck]="",IIf([LocationCheck]="","PASSED ALL CHECKS",""),""),""),""),"") AS CheckAll
FROM Qcv;
 

When I try to add criteria, ie:

WHERE ((Not (IIf([StartDateCheck]="",IIf([EndDateCheck]="",IIf([SlotStartCheck]="",IIf([SlotEndCheck]="",IIf([LocationCheck]="","PASSED ALL CHECKS",""),""),""),""),""))="PASSED ALL CHECKS"));

It asks me to define each of the terms (running the Access Query viewer), or in a report it says "Query is too complex."

I appreciate that there are easier ways of building this kind of query, but my dataset is relatively small (2600 entries), and I only need to run this report a dozen times a year. And... I'm completely new to SQL (started learning two weeks ago), so your patience in explaining is greatly appreciated.

What am I doing wrong? Is there a better way of filtering my data?

Thanks!

Joel

[NeoPa]

Joel,

I already explained in another thread that this should be done using LEFT JOINs. Now you're asking again if there is a better way available. Do you really thingk the correct answer is going to change depending on how many times you ask the question. I can confidently assure you it won't.

Unfortunately for your current approach the WHERE clause, quite sensibly, is processed before the SELECT clause. This means that the ALIASes (AS) you've defined in the SELECT clause are not available to the WHERE clause as you've tried to use them.

The format of a WHERE clause is :

WHERE ([X] = Y)
  AND ([Z] In (A,B,C)
  AND (etc))

What you have doesn't follow that simple approach so is fundamentally unrecognisable to the SQL engine (Hence the error message).