Yes / No field auto populate based on expiration date (Access 2003)

[Chris]

Hello,

I have been challenged with making a yes/no field either stay on or
off based and contract end date field in my table. I am using Access
2003.

So for example, my client would like to have a table with products
(text), contract end date (date), current date (date), renew (yes/no).

If the current date field is greater than the contract end date, then
i'd like the renew box to be checked in the table for that record. I
realize that I can just setup a simple query to show me the products
with contracts that are past the current date, but I was just
wondering if this was the only was I can set this up.

Thanks in advance,

Chris

 

[Jeff Boyce]

Chris

If you (and/or your client) can already tell that a record is due for
renewal (i.e., "current date is greater than contract end date", I don't see
the value in storing either the [CurrentDate] or the [Renew (Y/N)] value.

That might be how you'd do it in a spreadsheet, but Access is a relational
database.

If you create a query against a table that has a [ContractEndDate] field,
and you create a new field in that query, something like (untested):

Renew: IIF(Date()>[ContractEndDate],"Yes", "No")

and used "Yes" as the Selection Criterion under that (new) field, you'd have
a way to identify every record that was due for renewal.

Storing "current date" in your table is a maintenance headache ... every
day, you have to update that field.

Storing a Y/N value based on that is redundant, given the approach above.

Your client needs to focus on "what" (what do I need to know to run my
business?), rather than telling you "how" (and here's how you have to
program it...).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP