Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SORTING RECORDS INTO GROUPS 2

Status
Not open for further replies.

Evil8

MIS
Mar 3, 2006
313
US
I have to create a new report with employees listed under age groups:

00-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-64
65-69
70-74
75+

I already have a report with employees w/ ages. I'm just not sure how to set up the groups. As always your help is appreciated. Thank you.
 
i would probably create a formula that would evaluate the age and assign it a number and then group on the formula values.

As an example:
create a formula {@AgeCheck}
IF {Table.Age} in [0 to 24] then 1
else
IF {Table.Age} in [25 to 29] then 2
else
IF {Table.Age} in [30 to 34] then 3
else
IF {Table.Age} in [35 to 39] then 4
etc etc etc

You could also use SELECT-CASE in a formula to do the same thing.
{@AgeCheckCASE}
select {table.Age}
CASE 0 to 24 : 1
CASE 24 to 29 : 2
CASE 30 to 34 : 3
CASE 35 to 39 : 4
etc etc etc


 
Worked like a charm. Thank you very much! I knew I was overthinking it as I often do.
 
Note that you could set up the formula to use the label as in:

IF {@age} in 0 to 24 then
"00 - 24" else
IF {@age} in 25 to 29 then
"25 - 29" else
IF {@age} in 30 to 34 then
"30 - 34" else //etc.

Make sure your age formula results in an integer with no decimals. Otherwise you will lose people in between ranges, e.g. 24.6. You could also address this by using:

if {@age} < 25 then
"00 - 24" else
if {@age} < 30 then
"25 - 29" else
if {@age} < 35 then
"30 - 34" //etc.

-LB
 
Thanks, lbass. I used the case formula, but after your post I went back and modified it with the text strings. I should have known that...

This is my age formula:

if Month ({tbl_ClInsured.BirthDate}) < Month (CurrentDate) or
(Month ({tbl_ClInsured.BirthDate}) = Month (CurrentDate) and
Day ({tbl_ClInsured.BirthDate}) <= Day (CurrentDate))
then Year (CurrentDate) - Year ({tbl_ClInsured.BirthDate})
else (Year(CurrentDate) - Year({tbl_ClInsured.BirthDate}))-1

I don't think I missed anyone but I'll check.
 
i think your code it duplicate,

why you check 'day' if today is 12 oct and birthdate is 12 apr it 'true'
but when check 'month' it 'false'

i think your code it hard to read and understand,

if i do not misunderstand you, i adjust your code to..

//////////////////////

if Month ({tbl_ClInsured.BirthDate}) <= Month (CurrentDate) then
Year (CurrentDate) - Year ({tbl_ClInsured.BirthDate})
else (Year(CurrentDate) - Year({tbl_ClInsured.BirthDate}))-1

 
Here is Ken Hamady's age formula:

WhileReadingRecords;
DateVar Birth:= {ages.Birth}; // Replace this with your field for Date Of Birth
DateVar Ann := {ages.DateAnn}; // Replace this with CurrentDate to get their age as of the time of the report
//or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

Many age formulas fail when a birthdate falls on 2/29.

-LB
 
lbass, when I plug in the age formula you posted above I get the error: "A date is required here." on DateVar Birth:= {tbl_ClInsured.BirthDate};

But when I actually put the ({tbl_ClInsured.BirthDate}) into the formula rather than use the DateVar (Birth) it works without error.

Can you tell me why?
 
DateVar Birth:= date({tbl_ClInsured.BirthDate});

-LB
 
Thanks, I figured it would be a syntax thing.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top