Conditional Formatting Query

  • Thread starter Thread starter prosfora via AccessMonster.com
  • Start date Start date
P

prosfora via AccessMonster.com

I have a query that brings up a job#, its start date (mm/dd/yyyy), and its
priority status (Low, Medium, or High).

Is there SQL I can use to make another query that will look at the start date,
and if it is a week old, change the priority status to Medium, and if it's a
month old, change the priority to High?
 
Try the following UNTESTED SQL on a copy of your data. If it gives you the
desired results, then use it.

UPDATE [YourTable]
SET [Priority] = IIF([StartDate] <= DateAdd("M"-1,Date()),"High","Medium")
WHERE [Priority] In ("Medium","low")
And [StartDate] <= DateAdd("d"-7,Date())
 
Thanks very much John! I received code through another post that has worked
and is as follows, however, I'm very anxious to compare it to yours. This is
greatly appreciated!

(Other code):

SELECT JobID, StartDate, IIf((StartDate + 30) < Date(), "High", IIf(
(StartDate +7 ) < Date(), "Medium", [Priority])) AS [Priority Level]
FROM qryByPriority


John said:
Try the following UNTESTED SQL on a copy of your data. If it gives you the
desired results, then use it.

UPDATE [YourTable]
SET [Priority] = IIF([StartDate] <= DateAdd("M"-1,Date()),"High","Medium")
WHERE [Priority] In ("Medium","low")
And [StartDate] <= DateAdd("d"-7,Date())
I have a query that brings up a job#, its start date (mm/dd/yyyy), and its
priority status (Low, Medium, or High).
Click to expand...
[quoted text clipped - 4 lines]
a
month old, change the priority to High?
Click to expand...
Click to expand...
 
if the 'status' is solely dependant upon date - it's wiser to use a
calculated field

air calculation:

status: IIf(Date()-7>[start date],"Medium",IIf(Date()-30>[start
date],"High","Low"))
 
Back
Top