Calculations in a form

[Darhl Thomason]

I have a database built with Access 2003, it's in 2002/2003 format. I'm
trying to do what appears to me to be kind of a complex calculation that I
just can't quite figure out. Basically, I've built a database for my flying
club (we're all pilots). One of the rules to be a pilot is that you have to
have a flight physical every few years. But depending on how old you are on
your last flight physical dictates how long you can go before your next
flight physical. If you are less than 40 years old, then you can go three
years until your next physical. If you are 40 or older, then your next
physical can only be two years away.

I have a calculation that calculates your age based on your birthdate (field
name DOB). I also have the calculation that adds 2 years to your last
medical date (field name DateLastMedical). But that calculation really
needs to vary depending on the above. So the calculation should be
something like:

If age < 40 then NextMedicalDate = DateLastMedical + 3 years
else
NextMedicalDate = DateLastMedical + 2 years.

My current actual calculations are:
Age Calculation
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

Medical Calculation
=DateSerial(Year([DateLastMedical])+2,Month([DateLastMedical]),Day([DateLastMedical]))

 

[Nikos Yannacopoulos]

Darhl,

If I get this right, the part that's ouzzling you is how to determine
whether to add two or three years to the last physical date? Well, if
yes, one way is to evaluate Age>=40 (since you have already calculated
the age), and take advantage of the fact that the evaluation wil return
0 if false, -1 if true, use an expression like:

-(Age < 40) * 1 + 2

which will return 2 if under 40, 3 if 40+.

I would do this (together with the age calculation) in a function in VB:

Function Next_Medical_Date(DOB As Date, LastMedical As Date)
Dim vAge As Integer, vInterval As Integer
vAge = DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), _
"mmdd") < Format([DOB], "mmdd"))
vInterval = -(vAge < 40) * 1 + 2
Debug.Print vAge, vInterval
Next_Medical_Date = DateSerial(Year([LastMedical]) + vInterval, _
Month([LastMedical]), Day([LastMedical]))
End Function

HTH,
Nikos

 

[Darhl Thomason]

Hi Nikos,

Thanks again for your great help. This is slightly above my head, but I'll
give it a go and get back to you on how well I did.

;-)

Darhl

Darhl,

If I get this right, the part that's ouzzling you is how to determine
whether to add two or three years to the last physical date? Well, if yes,
one way is to evaluate Age>=40 (since you have already calculated the
age), and take advantage of the fact that the evaluation wil return 0 if
false, -1 if true, use an expression like:

-(Age < 40) * 1 + 2

which will return 2 if under 40, 3 if 40+.

I would do this (together with the age calculation) in a function in VB:

Function Next_Medical_Date(DOB As Date, LastMedical As Date)
Dim vAge As Integer, vInterval As Integer
vAge = DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), _
"mmdd") < Format([DOB], "mmdd"))
vInterval = -(vAge < 40) * 1 + 2
Debug.Print vAge, vInterval
Next_Medical_Date = DateSerial(Year([LastMedical]) + vInterval, _
Month([LastMedical]), Day([LastMedical]))
End Function

HTH,
Nikos

I have a database built with Access 2003, it's in 2002/2003 format. I'm
trying to do what appears to me to be kind of a complex calculation that
I just can't quite figure out. Basically, I've built a database for my
flying club (we're all pilots). One of the rules to be a pilot is that
you have to have a flight physical every few years. But depending on how
old you are on your last flight physical dictates how long you can go
before your next flight physical. If you are less than 40 years old,
then you can go three years until your next physical. If you are 40 or
older, then your next physical can only be two years away.

I have a calculation that calculates your age based on your birthdate
(field name DOB). I also have the calculation that adds 2 years to your
last medical date (field name DateLastMedical). But that calculation
really needs to vary depending on the above. So the calculation should
be something like:

If age < 40 then NextMedicalDate = DateLastMedical + 3 years
else
NextMedicalDate = DateLastMedical + 2 years.

My current actual calculations are:
Age Calculation
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

Medical Calculation
=DateSerial(Year([DateLastMedical])+2,Month([DateLastMedical]),Day([DateLastMedical]))