Automate true/false field

[thadson]

I have struggled with this for a while now and I finaly realized that I need some pointers, so please help.
I have a table called "membership" that contains the following fields:
custID, activationdate, active, canceldate
The "active" field is a true/false field.
"canceldate" is always a newer date than "activationdate".
I would like to set the active field automaticaly. It should be true only if:
1) there is no cancel date
or
2) if there is a canceldate, than active should stay true until customer's payment runs out. (Payments are made in advance for 1 month, every month, on the date of original day of subscription found in start date, meaning that if account started on 04/04/03 than customer makes payments on the 4th of every month afterwards like 05/04/03, 06/04/03, etc. If they cancel on the 5th of any month they become inactive on the 4th of next month.)
Is this possible to do and if it is than how?

 

[dcx693]

thadson, the first thing I would say is that rarely is it a good idea to store a field in a table that can be calculated from the existing fields in the table. It's "good practice" to store only basic data in a table and calculate what you need to see using a query or a form.

That said, it's not possible to do what you're asking without writing some type of query (or typing in the values manually), so you'd probably be better off dynamically calculating them anyway.

To calculate your active field in a query, you can use the IIf function (do some research in the Access help on that). The function works generally like this:
IIf(condition, result if true, result if false)

 

[thadson]

the formula

Thanks for the reply, I appreciate it.
I guess I was not clear enough, my most important problem is the formula itself. I'm not a programer and the logic of this (however symple it might be) is beyound me... So far however noone else could came up the right formula either. If I apply what you say than it could be something like this: Active(IIf(DatePart("dd",[CancelDate]) > (DatePart("dd",[ActivationDate]), "True", "False")) but how would this take into account that there are different months are involved... even different years in the future...