Questions about possible expressions

[king07]

Good Morning

I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:
1) Last Review date
2) Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)
3) Status

Now here is my question:

I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue--

Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?

Thanks for the help

Sorry if this is confusing.

 

[The_Doc_Man]

Don't do EITHER field in your table. Have the date of your last review as-is. Build a query that repeats all the fields in the table and includes two more expressions. Look up the italicized terms in Access Help if you don't know these functions already.

1. Look at DateAdd - to add a year to the projected review date based on the date of last review.

2. Look at a comparison of the projected date to the Date() or Now() functions. Use IIF to display the word OVERDUE when the due date is in the past.

Never try to compute anything that goes into a table if the method for computing it is a simple operation based on another data field. Your computer is far faster than necessary to compute such things as this on-the-fly and save you the space (and headache) of computing them for data entry.

Not to mention that there is some implication that the stored value might represent a table denormalization since one of the fields you display would no longer depend solely on the prime key of that table. (It would also depend on the value for Now() or Date(), neither of which are part of the key.)

 

[FoFa]

Depends on when you want the status to be queried and changed.
You can have it modified on the form itself when displayed, or you can not display the stored status, and use a calculated status for the form display if you don't want it physically changed. OR you can run a query periodically to update the status.

 

[Fifty2One]

i would also suggest to keep the STATUS as on the fly rather than a stored field. i did similar before and even had the font change colour as a duedate approaches like ForeColor = vbYellow when you are 14 days before exipiry and ForeColor = vbRed when it is overdue

 

[king07]

Okay. so i got the next review date figured out-- my next question with the IIf function, is it possible to have more than two outcomes?

i.e. our years go from Nov 1-Oct 31--
so if a review is done Nov 6/06, then the status would be complete-- as it is done for the fiscal year.

The options i want to come up under status are- completed, upcoming and overdue-- i just cant quite seem how to figure out putting in more than two outcomes--

any idea's?

 

[FoFa]

IIF(Cond1,Cond1true,IIF(Cond2,Cond2true,Else))