Simple but hard if you dont know

[fredfox]

I have created a database in which I have three Fields.
1. Date Joined
2. Renewal Date
3. Member/Renewal

When I type in the date a person joins our club, I have made the renewal date come up displaying a year ahead, when renewal of membershiip is due.

I would like to be able to put in the date joined date and have the field 3 show Member and then when the renewal date is reached, to have in automatically change to Renewal.

It is probaly a simply formula but I can not get it right.

Please Help.

Foxy

 

[supercharge]

Example please...

Can you post your example database for us to see?

 

[The_Doc_Man]

When you use Date() function, you can compare the result to other dates.

Like, ... the DateAdd function (look up in Help Files) to compute a date one year later than Date Joined.

BTW, you may have redundant data or you may have an omission. What do you do if

a. The member renews early.
b. The member renews late.
c. The member declines to renew.

You need other data to track this. And if you do have this, then your formula becomes simpler because you don't have to play as many date tricks.

I would have two stored dates and a couple of status flags.

One date = original membership.
Other date = last membership payment date. (Which could match "original" date.)
Flag = member is current.
Flag = member has renewed.
Then I would show the anticipated renewal date as a computed field in a query or form, equal to 1 year (DateAdd again) after last payment date, whatever it was.
When the member pays again, update the last membership payment date, leaving the original membership date alone forever.

Now, if you wanted payment HISTORY, these fields don't even belong in the same table. I would suggest in that case, you need to read up on NORMALIZATION.

 

[Rich]

You don't need to store the renewal date, a calculated field in a query will do what you want.

 

[fredfox]

Thanks for your help and time, I will remove all the personal data and send a copy to you as soon as possible. Have you got an e-mail address.

John

 

[Mile-O]

Create a new database, import only the relevant objects, remove any confidental data, zip it, and upload it here - don't expect anyone to want your database in their inbox.

 

[fredfox]

problems with database

Thanks for your offer of help.

You will see it is a simple problem if you know what your are doing.

When entering the joining date I would like the Status to change automatically to Member and when the renewal date is reached I would like the status to automatically change to Renewal.

Many Thanks,

Fredfox.