Query to set status based upon 2 fields

[natedog51]

I'm trying to create a report eventually, however, I need to get my query set up.

My report should look like the following when complete.

Timeframe (mth,qtr,year)
# of Total Projects Completed - regardless of status
# and % of Projects Completed within Requested Delivery Date
# and % of Projects Completed within Committed Delivery Date
# and % of Projects Completed within Requested and
Committed Delivery Date


I have 3 different fields to base my calculation from:
Actual Delivery Date
Requested Delivery Date
Committed Delivery Date

I have created 2 new fields that calculate the number of days:
[Actual Delivery Date] - [Requested Delivery Date]
[Actual Delivery Date] - [Committed Delivery Date]

I'm trying to set up a "Status" field to indicate whether the project was completed "Within Requested", "Within Committed", or "Within Requested and Committed", however, I'm stumped. I've tried an Iff statement to no resolve. I don't want 10 queries just to get me there either.

Please help!

Thanks!

 

[Pat Hartman]

Do the calculations in a query:

Select ...., [Actual Delivery Date] - [Requested Delivery Date] As ReqDeviation, [Actual Delivery Date] - [Committed Delivery Date] As CommDeviation, IIf(ReqDeviation < 10 AND CommDeviation < 10, "Within Requested and Committed", IIf(ReqDeviation < 10, "Within Requested", IIf(CommDeviation < 10, "Within Committed", "Out of Range")))
From YourTable;

I used 10 as the number of days since you didn't say what your value is.

 

[natedog51]

Works like a CHARM! Thanks so much for your help!

I just wish I would have asked earlier rather than spending 4 hrs racking my brain.

Thanks Again