I ran a query on a query and that has worked perfectly for me. Is this the wisest way to do it?
That is fine. I use this technique a lot. Sometimes you can try to do too much in one query, and Access can choke on it.
What I often do is if I have queries that do calculations, and then I want to use some of those calculations in my criteria, is I will have one query that does the calculations, but has no criteria. Then I will do a query of that query where I add my calculations.
If you had wanted to do it all in one query, the thing to remember is that Access does not like to use Aliases of calculated fields in the Criteria section of the same query. So, if you substitute the "planned" alias in your query with the actual calculation, it will work. So that query would look like this:
Code:
SELECT
DateCalc.PlanStartDt,
DateCalc.PlanCompDt,
DateCalc.ActualCompDt,
DateCalc.ID,
DateCalc.Status,
DateCalc.Flag,
IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))) AS Planned,
IIf([planned]="completed",[actualcompdt]-[plancompdt],"") AS Overdue
FROM
DateCalc
WHERE
(((IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))))="completed")
AND
((IIf(IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))) ="completed",[actualcompdt]-[plancompdt],""))=[actualcompdt]-[plancompdt]));