calculating expected student grade level from DOB 2

[bmacdo]

[using Crystal 10 via MS-SQL Server]

I've been asked to add a formula to a report of expected student grade level. The report currently has parameter fields to select those who are in our programs w/in a certain date range. Date of Birth is an available field. Grade 1 would include any child who is six the preceding Feb. 1.

I've found formulas in this forum to calculate age, but the Feb. 1 aspect is throwing me off in calculating grade level.
Thanks for any help.

 

[MrBillSC]

To calculate age as of Jan 31, try the following:

// formula Age:
If Month({table.BIRTH_DATE}) > 1 then
DateDiff("yyyy",{table.BIRTH_DATE},Today) else
DateDiff("yyyy",{table.BIRTH_DATE},Today) + 1

MrBill

 

[bmacdo]

I used the formula, substituting the report's parameter begin date (7/1/07) for Today. I see ~Johnny's birthday is 1/15/1992 and the result of the formula is 16. But he would be 15 on the preceding 2/1/07, no?

If Month({table.Client_DOB}) > 1 then
DateDiff("yyyy",{table.Client_DOB},{?Begin Date}) else
DateDiff("yyyy",{table.Client_DOB},{?Begin Date}) + 1

Brad

 

[lbass]

Try changing that to:

If date(year({?Begin Date}),month({table.Client_DOB}), day({table.Client_DOB})) > date(year({?Begin Date}),1,31) then
DateDiff("yyyy",{table.Client_DOB},{?Begin Date}) else
DateDiff("yyyy",{table.Client_DOB},{?Begin Date}) + 1

-LB

 

[bmacdo]

Alas, ~Johnny is still showing up as 16 when I use Begin Date 7/1/2007.

Brad

 

[lbass]

Sorry, try this:

if date(year({?BeginDate}), month({table.dateofbirth}),day({table.dateofbirth})) >
date(year({?BeginDate}),1,31) then
datediff("yyyy",{table.dateofbirth},date(year({?BeginDate}),1,31)) - 1 else
datediff("yyyy",{table.dateofbirth},date(year({?BeginDate}),1,31))

-LB

 

[bmacdo]

Voila. You're the best. Thanks again.

Brad

 

[bmacdo]

erp! When I expanded the report to include more than one facility, I got an error message regarding a birthdate of Feb 29 in a leap year:

"A day number must be between 1 and the number of days in the month."

The DOB indicated in the error was #1992-02-29#. 1992 was a leap year, so I don't see how the DOB could be in error.

Brad

 

[lbass]

Sorry, datediff doesn't really accommodate leap years. I should have suggested Ken Hamady's formula in the first place:

WhileReadingRecords;
DateVar Birth:= {table.dateofbirth};
DateVar Ann := date(year({?BeginDate}),1,31);

if (Month(Ann) * 100) + Day (Ann) >=
(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

-LB

 

[bmacdo]

I really am grateful your help with several challenges including this one.

Brad