Conditional Formatting Query

[mestst64]

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?

Thanks!!

 

[RuralGuy]

Create your own function in a standard module that takes a date and returns a string based on your criteria and call in from your query.

 

[mestst64]

RG,

Thanks. Forgive me but I'm new to SQL, could you elaborate, or provide a brief example?

much appreciated---

 

[RuralGuy]

If the priority is a function of the StartDate and the Current date then it should *not* be a field in the table. That would be storing a calculated value, which I'm sure you know is a no-no. It should be calculated each time. Do you need assistance with the VBA for the function you need?

 

[Pat Hartman]

You are storing PriorityStatus when it should be derived. Since it is dependent upon a date, it could potentially change from day to day. To overcome this you would need to run an update query every single day. This is poor practice. Remove the status field from the table and calculate it on the fly.

Select ..., IIf(StartDate +7 >= Date() AND StartDate +30 <= Date(), "Medium", IIf(StartDate + 7 <= Date(), "Low", "High")) As PriorityStatus
From YourTable;