Hey guys,

Okay, so lets say I've got the following scenario.

I want to find all matching records where a persons date of birth, gives them a certain age by a certain date..

For example:

Date of birth: 3/17/89

Table...

[color:red]Age|[color:blue]Based on Date

[color:red]18|[color:blue]3/16/07

[color:red]17|[color:blue]3/16/07

[color:red]18|[color:blue]3/19/07

In the above example, records 2 and 3 would idealy be returned...

Cheers guys.

Edited August 22, 200718 yr by Guest

Yup. That's a global table for the evaluated date and a calculated field on the table with the person's age to determine age from the eval date. Takes about five minutes as long as you're roughing the age calc. If you want to do the crazy calcs for EXACT age, it's going to take a little while longer to write out the custom function but shouldn't be that bad. If you'd like the *extremely* simplistic example that I did, I'll upload it but I've got a hunch that you'll figure this one out in a millisecond.

The fast and dirty version of the calc is:

GetAsNumber(globals::eval_date - person::date_of_birth) / 365

Of course this is an integer approximation but the calc to calculate exact age is out there. I think that there's one on Dunning's site.

Edited August 22, 200718 yr by Guest

Your question is not clear. You have a single date of birth, while each record has its own "Based on Date" value - that doesn't make much sense to me.

Its a minimum age required based on a certain date... for example

If my date of birth was 3/17/89 and I had to be 18 based on the date 3/16/07, then I wouldn't be and that record would be rejected.

If I had to be 17 based on the date 3/17/07 then I would be.

So each of these records have a date value from which the age must be calculated.

Hmmm, okay, rephrase.

Based on the current date i am 18... Based on the "Based on Date" field if the value was 12/12/07, I am still 17 ...

I need to return all records where I meet the minimum age based on the "minimum age" field where my age is not calculated from "today" but rather from the value contained in the "based on date" field.

Mike: I unfortunately don't have the option of using a global field in this case.

Edited August 22, 200718 yr by Guest

It's still confusing. Suppose you have a table of people, and each person has a date of birth. To find people who are (or were, or will be) at least x years old on date y, you would calculate the maximal date of birth that would satisfy this condition (i.e. date y minus x years), then search the date of birth field for values less than or equal to the result.

If your situation is different, elaborate some more.

Okay, lets make up a scenario.

Suppose we have minor league baseball - that's what the kids play in america right?

Suppose that the season starts 3 times a year for whatever reason. The three entry periods are 3/1/07, 6/1/07, 9/1/07. There are 3 divisions: Junior: Ages 5-10, Teen Ages 11-15 and Young Adult: Ages 16-18.

Lets say registration opens up on the 1/1/07.

Now lets say my birthday is 7/1/91 making me 15 at the start of the year, 15 the first entry, 15 the second entry and 16 on the third entry. I would be in the "teen" division for the first 2 entry's and Young adult for the 3rd entry.

Now I want to find my appropriate division in the case of all three entry points.

This subject has come up in the past. Take a look at this Link, and the file by Ray, and I also uploaded one.

HTH

Lee

That's an entirely different question - there would be no need to perform a find here.

Assuming there is a Registrations join table between Players and Seasons, it should be trivial to calculate the proper division of a registration, based on the two related fields Players::DateOfBirth and Seasons::StartDate.

Okay, that would normally be the case provided there was only one possible division per age group, but it seems my example left some details out.

What if "non competitive" divisions exist, or "round robins" or other things like that -- other categories also, but that's one example... or what if the child doesn't match any divisions at all i.e. they are 19?

You would need to decide on a workflow to determine the best way to deal with those situations. For example, you could show eligible registrants in a portal (based on cDecisiveDate ≥ DateOfBirth).

The important point here, I think, is this: on any arbitrary date (say August 22, 2007), the people who celebrate their 10th birthday were born exactly 10 years before (August 22, 1997). Therefore, anyone who was born on August 22, 1997 or earlier, was at least 10 years old on August 22, 2007.

You would need to decide on a workflow to determine the best way to deal with those situations. For example, you could show eligible registrants in a portal (based on cDecisiveDate ≥ DateOfBirth).

Can't do it that way unfortunately - its CWP entry - the person who registers the child also registers them for the sport...

Maybe I'll just scrap the idea and show all divisions instead and let them guess and manually re-assign if necessary... or have an open and close registration date or something like that.

Cheers Michael.