Setting a field based on date in another field

[naomi1017]

I have a database with a date field of 'ReviewDate'. In the same table there's a text field of 'Expired'. I want to set the default value of 'Expired' to be "Y" when the reviewdate has passed, or "N" when it hasn't passed. Users may also enter other options ("A" for waiting or "H" for on hold).

If ReviewDate< Now(), then Expired = Y
If ReviewDate> Now(), then Expired = N

My thought was do this with an expression:

=IIf([tbl]![ReviewDate]>Now(),"Y","N")

but the IIf option is only available in a form, not a table.
so...

Any ideas on how I would do this?

 

[AccessOfEvil]

Depending on your version of Access you might be able to put a calculated field in a table. However, the fact that users can change the field directly would mean you probably don't want to do that.

You can use your logic of =IIf([ReviewDate]>Now(),"Y","N") within a query if you needed to display or filter on expired reviews (for example, to run a report).

It gets trickier with maintaining status of Expired though. First of all, users shouldn't be entering data directly into the table; they should use forms (so you can do a little preventing of them mucking with the data).

You would then need to clearly define rules, for example - if a item is on hold, and ReviewDate passes, does it still expire? If not, does it expire the instant it goes off hold? Presumably not, so then how is it handled? (note I'm not looking for an answer to this, just to give you an idea of what to think about).

As far as updating the field automatically, there would be several ways to do it, depending again on your needs. You could just update it when anyone opens a certain record (check if the date has passed, if so, then update to "Y"). You could run a batch update daily or weekly on the whole table, or when a certain form or report is opened, etc.

 

[naomi1017]

You're right users shouldn't be in the tables... they update this information via a form. So the problem became how could I set the form to show a default value and then input that value back into the table? I could get the form to display the default value:

Control Source =IIf([ReviewDate]>Now(),"N","Y")

But not to record it in the table. I thought the answer was in setting the default data, as that way when an exception occurred (setting the field to 'H') it won't get overwritten the next time someone opened the table. But again, I couldn't find a way to set the default data with a conditional statement.

________________________________________________________
So I went back and re-read your answer, which mentioned an update query. And, just in case someone needs to answer this same question years from now, here's how I fixed it.

Using the information on the MS Office website (officemicrosoft.com update-data-by-using-a-query-HA010342081.aspx) I created an update query that had:

criteria = NOT "A" OR "H"
Update to = =IIf([ReviewDate]>Now(),"N","Y")

Saved that Update Query, ran it to test. Went back to my form. On the form I selected Event tab on the Properties sheet. From there I picked 'On Load' and clicked the '...' to get to the builder. I picked Macro Builder, and created a Macro with one line - "Open Query" and set it to open my Update Query.

Now whenever a user opens the form, they get a warning that they're going to update the data. It runs, and then they can go in and make any manual changes. That information is recorded in the table, so I'm free to run reports off of it.

Please tell me if I've missed something horrible, because from here it looks like I'm done.

 

[AccessOfEvil]

From what I understand this looks reasonable. You could get into some more elaborate coding, but this should suffice.

You might double check the logic of the criteria. You probably want to use

Not ("A" or "H")

instead of

Not "A" or "H"

or even try

Nz([Expired],"") Not In ("A","H")

which makes sure null values for Expired compare correctly (the "In" is just a slightly different way of doing the "Or" logic and you are less likely to make a mistake with parentheses).

 

[Galaxiom]

Your description indicates a normalization error. The Expired condition should be calculated from the ReviewDate in a query or calculated control on the forms and reports.

The A or H state can overide the Y or N calculation. However the logic has not been described clearly enough to advise further.