If function? Maybe?

[Annette Marie]

Hello. Forgive me if this is being posted in the wrong area or if it is a stupid question....this is my first attempt at creating a database with FM so I have LOTS to learn.

I have a two fields in my database, one is date of birth and another is age. Age gives me the age in years and months.

What I'm trying to do is create a field which for now I'll call Age Group that determines which group out of three the person fits in based on their age.

The three groups I need are:

Under 16's

16-18

Over 18's

So I want a calculation that puts the appropriate group according to the age.

Is this possible?

[Lee Smith]

When do they join the age group, On their Birthday, or a selected month of the Year, such as September, January, June,... ?

Lee

[Fitch]

Your age field is probably a text field if it shows years and months. For your requested calculation we need a number field that represents age in years. You'd then create something like this:

Case(

   AgeInYears < 16 ; "Under 16's" ;

   AgeInYears < 19 ; "16-18" ;

   "Over 18's"

 )

[Lee Smith]

Hi Tom,

Not sure that the Age in Years would be more simpler than just using a Get(CurrentDate) and the Birthday to calculate the new [color:blue] Age Group Field.

I could be wrong, but I think that there is more to this request than we see here. That is why I asked if there was a month for a cutoff involved.

Lee

Edited September 13, 2010 by Guest

[Fitch]

Yes, I was just trying to address the question as stated, without going into the problem of how to derive the age. There are at least a couple of ways to do that, e.g.

Let(

  days = Get( CurrentDate ) - date of birth ;

  Int( days / 365.25 ) 

   )

But since Annette Marie already has a field for age (in years and months), I figured maybe she already has a method that she can use to get the age in years -- as a number -- and then plug that into a Case calc.

I don't see anything about a cutoff date, but if there is one, you'd just use that instead of Get( CurrentDate ), no?

[Annette Marie]

Sorry I've taken so long to respond.

Basically I have two fields at the moment. One where you enter the date of birth, the other gives you the persons age in years and months based on that DOB. It will say 18 years, 6 months.

I would need it to move to the group based on their age not a particular month. So anyone that says 15 years, 11 months or less would be in group under 16's; from 16 years 0 months - 18 years 11 months would be 16-18; and 19 years, 0 months and up would be Over 18's. Etc.

[Lee Smith]

Then fitch's first calculation should do it.

[Raybaudi]

...the other gives you the persons age in years and months based on that DOB. It will say 18 years, 6 months.

Try:

Let(

Y = LeftWords ( YourAgeTextField ; 1 ) ;

Case(

Y < 16 ; "Under 16's" ;

Y < 19 ; "16-18" ;

"Over 18's"

)

)

Note that it is the same calculation gived by Fitch modified to get the year.

[Annette Marie]

I'll try that tomorrow when back in the office. Thanks so much everyone for all the great help!

[Annette Marie]

The calculation you gave me Fitch worked great. But when I tested it and changed the date to see if it changed the group it just stayed on the original group it was placed in? How do I get it to refresh, say if the wrong DOB was entered or as they get older and move into the next group?

[Annette Marie]

Sorry..update..I just entered a client who is only three and it placed them in the over 18's group.

[Raybaudi]

Sorry..update..I just entered a client who is only three and it placed them in the over 18's group.

Try:

Let(

Y = [color:red]GetAsNumber ( LeftWords ( YourAgeTextField ; 1 ) [color:red]);

Case(

Y < 16 ; "Under 16's" ;

Y < 19 ; "16-18" ;

"Over 18's"

)

)

[Annette Marie]

That last one worked perfect!!

Thanks SOOOOO much!