Add Date to Date

[Altamedic]

Hi There,

Sory I have been looking forever, and all I can find is how to add todays date to a current date.

We run a training facility and ever 12 or 36 months thier certification runs out, I have created a formula

If (Attended = 1 ; Date ( Month ( Event Date ) + 36 ; Day ( (Event Date) ) ; Year ( ( Event Date)) ); "")

Attended ; That they attended

Event Date: The day of the Course

36 = months this certification is good for ( I would like to be able to change is say as a varible depending on the course... IE First Aid is good for 36 Months, WHMIS is good for 12 Months, etc.)

And I keep coming up with todays date + 36 Months any help?

[comment]

Your formula should work, so the problem must be somewhere else.

36 = months this certification is good for ( I would like to be able to change is say as a varible depending on the course.

If you add a field for the duration, you can use =

Date ( Month ( Event Date ) + Duration ; Day ( (Event Date) ) ; Year ( Event Date ) )

to calculate the expiry date.

[Altamedic]

Many Thanks for your help thus far, I have changed my course table to reflect months, not years, which might be my problem, the formula that I am using is

If (Attended = 1 ; Date ( Month ( Event Date + Course Names::Valid Months) ; Day ( (Event Date) ) ; Year ( ( Event Date)) ); "")

Any suggestions

[comment]

Can you post a simple file showing the problem?

[Altamedic]

Absolutely, here is all the info taken out, with 1 course added

because of size 1.1 mb here is the link http://files.me.com/sarmstrongemt/odzw7k

[comment]

I am getting a "This file is no longer available." error screen.

[Altamedic]

Err try http://files.me.com/sarmstrongemt/p2dfw1

Edited May 13, 2010 by Guest

[comment]

Password?

:idunno:

[Altamedic]

F... if its not one thing its the other 3231

[comment]

That's a simple file? I am not going there...

However, I can see that the formula is different than the one in your original post. You now have:

Month ( Event Date + Course Names::Valid Months)

which should be:

Month ( Event Date ) + Course Names::Valid Months

And why is the calculation global?

[Altamedic]

Well because the course that the person takes is Maybe for example a Standard First Aid Course (3 Year Expiry) and a CPR course (1 year) Expiry

When I enroll the student, I enroll them in the class, I have a feeling that everyone is getting 3 years.

Do you have a suggestion on how I can simplify this?

[comment]

Global fields have the same value for all records in the table.

Do you have a suggestion on how I can simplify this?

Start from scratch? I can't make heads or tails of your file. I believe it could be MUCH simpler, but I am not sure what exactly you are tracking. What is the difference between a course and an event?

[Altamedic]

I could start from scratch!!

An event is a course that we put on we usually fall these FA-2010415 (First Aid Course 2010-04-15), among this event we have the opportunity to run 3 simultaneous classes such as Standard First Aid, Emergency First Aid or CPR.

We had an access program that worked good, it was locked so I could not edit things that needed to be edited.

Do you know of a Template, or should I Start with New Tables, etc, and avoid altering the alreday done by FM to fit my needs?

[comment]

IMHO, the FMI templates carry a lot of excess baggage - that's why it's preferable to start from scratch. I know the layouts are tempting, but function comes before form. I also suggest you think very carefully about the structure before creating anything.

Another point: make sure all your tables have a unique ID field (e.g. CourseID, EventID, etc.) with auto-entered serial number, and use these for your relationships instead of names or any other meaningful data.