Setting a value of the record based on date

[alikazim]

Hi All,

I've got a members table where I maintain membership's expiry date. There is also an 'membership expired' boolean (yes/no) field in the same table.

I want to set the value of 'membership expired' boolean field to 'yes' if the system date is more than the expiry date, automatically.

What is the best way to achieve this functionality.

[jimatqsi]

In what context are you considering doing this? You want a query to do a mass setting of all the records meeting the criteria? Or are you wanting to do this one at a time on a form as a record is edited? Only when new records are added?

Give us an overview of the task. What you want is simple once we know the process(es) it will occur in.

Jim

[alikazim]

Running a massive update query based on this check (expirydate > systemdate) is easy. But it requires someone to run the query.

Can we not have a process in place where it automatically handled by Access i.e. as soon as member's expiry date lapses, membership expired becomes yes?

[patjones]

It's somewhat redundant to have the boolean field in the first place. If you think a little bit about what you will use that boolean field for, you'll realize that you can accomplish the same task by simply doing the date comparison at the time that you need it.

For example, if you want to run a report to find out who is currently expired, you'll just put that date comparison in the filter argument for your report, or in the WHERE clause of the SQL that the report is based upon...which is essentially the same thing.

Another way to look at it is that if you retain the boolean field, it will almost always contain out of date information. Suppose you go the route of running a mass update query to update the whole table. The column will remain up to date only to the extent that you run the query every day, or have some other process that triggers the update query on a regular basis. This just adds to the complexity of your application.

I would thus eliminate the field. But this is simply my opinion.

Pat

[alikazim]

Thanks for your replies guys. Really appreciate it.