Status based on a time frame

[gregomel]

Hello...

I have a query that pulls an issue date. It also pulls a column that says "Late" or "On-time". This pulls from a source that is older so late/on time status is based on 5 days. It is now based on 3 weeks. I would like for any record with a date in the past 3 weeks to say "3 weeks" in the status column. Everything else can be "late." Basically everything issued today (and anything issued in the past 3 weeks should say "3 weeks') everything else is late.

This is formula I'm currently working with is:

IIf([DWG/RDDP Issued to Supplier Date]>DateAdd("d",-21,Date()),"Late","3 weeks")

It worked at first but now is not working. Also, what is the appropriate location for this formula? I had it in the first criteria box but it then only worked if it was in the second one. Any suggestions?

 

[SydneyGeek]

The expression needs to go in the top row of the query grid, not the Criteria row. Also, you may find it useful to give the field a name for later use --

TimeStatus:IIf([DWG/RDDP Issued to Supplier Date]>DateAdd("d",-21,Date()),"Late","3 weeks")

Denis