Age Calculation in the future

[jomonto]

Hello-

I've looked at all the age calulation solutions - but I'm a little
stumped on how to set this one up. Here is what I need to do.

I need to find all the kids in my DB that are currently 11 or 12 years
of age
AND
All the kids who WILL BE 11 on or before Dec 31, 2005.

The current kids aren't an issue - but how do I calculate who is 10 now
and will have a birthday between tomorrow and 12/31/05 ???

Appreciate any suggestions,
john-

[Brian Wilson]

<jo*****@zeus.kern.org> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hello-

I've looked at all the age calulation solutions - but I'm a little
stumped on how to set this one up. Here is what I need to do.

I need to find all the kids in my DB that are currently 11 or 12 years
of age
AND
All the kids who WILL BE 11 on or before Dec 31, 2005.

The current kids aren't an issue - but how do I calculate who is 10 now
and will have a birthday between tomorrow and 12/31/05 ???

Appreciate any suggestions,
john-

To find the kids who are 10 now but will be 11 by the end of the year is
easy enough, but do you realise that some of the kids who are currently 11
or 12 may no longer be 11 or 12 by the end of the year?
Imagine you write a function which takes the kid's date of birth and the
'EventDate' (could be today or a date in the future) and it returns the
number of years old. If you want to generalise the function so it handles:
How old will the person be at the end of this year? How old was the person 5
years ago? you will need to think about negative numbers. My function
ignores dates in the past and simply returns zero.
If there is a field for date of birth called [DOB] you could write:
AgeNow:AgeOnDate(Date(),[DOB])
AgeAtYearEnd:AgeOnDate(DateSerial(Year(Date(),12,3 1),[DOB])
Public Function AgeOnDate(dteDate As Date, dteBirthDate As Date) As Long

If dteDate > dteBirthDate Then
AgeOnDate = DateDiff("yyyy", dteBirthDate, dteDate) - _
IIf(Format(dteDate, "mmdd") < _
Format(dteBirthDate, "mmdd"), 1, 0)
End If

End Function

[Bob Quintal]

jo*****@zeus.kern.org wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

Hello-

I've looked at all the age calulation solutions - but I'm a
little stumped on how to set this one up. Here is what I need
to do.

I need to find all the kids in my DB that are currently 11 or
12 years of age
AND
All the kids who WILL BE 11 on or before Dec 31, 2005.

The current kids aren't an issue - but how do I calculate who
is 10 now and will have a birthday between tomorrow and
12/31/05 ???

Appreciate any suggestions,
john-

take your 11 years off of Dec 31, 2005.
that gives you Dec 31, 1994
compare that date to the birthday. if the birthdate is less,
they will be 11 by Dec 31.

If you also calculate the makimum date that a kid can be and be
12 today, (that's October 19,1992)
you can select all kids by testing for

Birthdate between #10/19/92# and#12/31/94#

--
Bob Quintal

PA is y I've altered my email address.

[jimfortune]

jo*****@zeus.kern.org wrote:

Hello-

I've looked at all the age calulation solutions - but I'm a little
stumped on how to set this one up. Here is what I need to do.

I need to find all the kids in my DB that are currently 11 or 12 years
of age
AND
All the kids who WILL BE 11 on or before Dec 31, 2005.

The current kids aren't an issue - but how do I calculate who is 10 now
and will have a birthday between tomorrow and 12/31/05 ???

Appreciate any suggestions,
john-

To get those who are 11 or 12 now plus those who will be 11 on
12/31/05:

SELECT * FROM tblKids WHERE Int(Format(Date(),"yyyy.mmdd") -
Format([BirthDate],"yyyy.mmdd")) = 11 OR Int(Format(Date(),"yyyy.mmdd")
- Format([BirthDate],"yyyy.mmdd")) = 12 OR Int(Format(DateSerial(2005,
12, 31),"yyyy.mmdd") - Format([BirthDate],"yyyy.mmdd")) = 11;

To get just those who are 10 now but 11 on 12/31/05

SELECT * FROM tblKids WHERE Int(Format(Date(),"yyyy.mmdd") -
Format([BirthDate],"yyyy.mmdd")) = 10 AND Int(Format(DateSerial(2005,
12, 31),"yyyy.mmdd") - Format([BirthDate],"yyyy.mmdd")) = 11;

Note that the second SQL statement uses AND instead of OR. Creating a
public function isn't much shorter. I didn't test it much. You can
incorporate Brian's Year(Date()) idea into it also instead of using
2005. Bob's idea looks good too.

James A. Fortune

[Allen Browne]

There's an Age() function here:
http://allenbrowne.com/func-08.html

It lets you specify an optional date to calculate the age at, using today if
you don't specify one.

The article also explains how several common approaches don't work reliably.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jo*****@zeus.kern.org> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hello-

I've looked at all the age calulation solutions - but I'm a little
stumped on how to set this one up. Here is what I need to do.

I need to find all the kids in my DB that are currently 11 or 12 years
of age
AND
All the kids who WILL BE 11 on or before Dec 31, 2005.

The current kids aren't an issue - but how do I calculate who is 10 now
and will have a birthday between tomorrow and 12/31/05 ???

Appreciate any suggestions,
john-

[jomonto]

Thank you all very much - excellent sugestions - I'm writing the report
right now, I'm sure it work great. I will study up on these solutions
and try to get a better grasp of date calculations.... this
time-machine thing always confuses me <smile>

Brian) - This report is actually a two-part request... One mailing is
to all the 11 and 12 year olds (today) and then a second group of
letters goes out to the kids who will turn 11 by 12/31.

thanks again,
john-