Date Problem

[cbouse]

hey all, beginner using version 8.5 advanced. doing some work for a non-profit group with membership date problems.

I currently have three date fields im working with;

date first joined

renewal date

expiration date

im doing a very basic script that looks at the date first joined and adds 365 days to it and puts that value in the expiration date field. if there is a value in the renewal date i want that to take priority and then add 365 days to that date and put that value in the expiration date field. The date first joined turns into historic data only after the first year.

Im not sure if anyone has an advanced calculation that they wouldn't mind giving me, cant pay for it, this is for non-profit, but the ideal situation for this calculation would be to have the date first joined, (this field just turns into historical info after the first expiration date) some sort of selector that would allow someone to renew a membership for multiple years, a renewal date field, and an expiration date field.

this would allow someone who just joined to pay for multiple years giving me a proper expiration date. The membership is for one full year so it would have to consider how many days are left in the year, not sure about leap years???

But people who renewed would also need the same option. Im not that smart so im just doing the good old "take this day and add 365 days to it". That will work without being too fancy, but if anyone can help me out that would be great....

Thank you,

Chris

[comment]

I believe you should create a record in a related table for each subscription - whether new or a renewal. The way you are going, you have no trail and no way to recover if you make a mistake.

[cbouse]

creating a record of all dates entered?

[comment]

Not sure what you mean.

[cbouse]

sorry, like i said, NOVICE. maybe i just need greater help than a forum can offer me....

[comment]

Maybe, but the issue here is not your skill level, only that the question seems out of context and is rather cryptic.

[jwjay]

Yeah, you would have a membership table with a membershipID field (unique identifier)

You would then have a related table with the membershipID, creation timestamp, renewal date, number of years subscribed, expiration date. The membership table would then show the last entry for that membershipID.

Calculation for the Expiration date would be:

Date(Month(renewal date); Day(renewal date); Year(renewal date) + number of years subscribed)

[cbouse]

that's great, thanks, ill give that a try.

thanks again for the help