Using Age as criteria. How would I do this?

  • Thread starter Thread starter Jerome
  • Start date Start date
J

Jerome

Hi,

I've a table of people (names, date of birth, etc) and one of activities
(name, genre, date, etc).

I'd like to select now the people corresponding to a certain age group
for a certain activity.

Like: List all the kids who, on the date of the activity, are at least
(x=)12 and at the most (y=)14 years old.

I've got to create a date formula I guess but I'm not really an expert
there. Something like Between(DateActivity - y years + 1 day) and
(DateActivity - x years) ... Can anybody help me expressing this in code?

Thanks a lot,

Jerome
 
Here is a function that calculates a person's age:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = year(DateToday) - year(Bdate) - 1
Else
Age = year(DateToday) - year(Bdate)
End If
End Function

If you plan to use a query, you can call a function from a calculated field
in a query. The function needs to be Public and in a standard module.
 
Hi Jerome!

it's will be an sql query that looks like:

SELECT * FROM tbl_People
WHERE Year(Date())-Year(birthDate)>=12
AND Year(Date())-Year(birthDate)<=14

hope that's what you've asked
 
That will not return correct results. Assumming Birthday is #7/1/1980#,
Year(Date())-Year(birthDate) returns 108.
In addition, it makes no allowance for the fact that the person's birthday
for the current year has not yet passed, so even if you used the correct
formula,
datediff("yyyy",birthday,date)
It would still be off by a year.
 
Jerome said:
I've a table of people (names, date of birth, etc) and one of activities
(name, genre, date, etc).

I'd like to select now the people corresponding to a certain age group
for a certain activity.

Like: List all the kids who, on the date of the activity, are at least
(x=)12 and at the most (y=)14 years old.

I've got to create a date formula I guess but I'm not really an expert
there. Something like Between(DateActivity - y years + 1 day) and
(DateActivity - x years) ... Can anybody help me expressing this in code?
Click to expand...


You don't really care about age, you can use the birthdate
instead.

SELECT kid
FROM table
WHERE [Activity Date] Between DateAdd("yyyy", 12, BirthDate)
And DateAdd("yyyy", 14, BirthDate)
 
Thanks everyone for answering but I think I have to rephrase my question!

My users want to have two fields on their form where they can enter the
age bracket of the kids, let's say 12 and 14. And those numbers should
be used to get the list of kids who will have, at the date of a certain
activity, at least 12 years and at the most 14 (or 15 minus 1 day).

That's why I thought about [dateActivity]- x years etc.

Any new ideas? Is that doable?

Thanks a lot!


Marshall said:
Jerome said:
I've a table of people (names, date of birth, etc) and one of activities
(name, genre, date, etc).

I'd like to select now the people corresponding to a certain age group
for a certain activity.

Like: List all the kids who, on the date of the activity, are at least
(x=)12 and at the most (y=)14 years old.

I've got to create a date formula I guess but I'm not really an expert
there. Something like Between(DateActivity - y years + 1 day) and
(DateActivity - x years) ... Can anybody help me expressing this in code?
Click to expand...


You don't really care about age, you can use the birthdate
instead.

SELECT kid
FROM table
WHERE [Activity Date] Between DateAdd("yyyy", 12, BirthDate)
And DateAdd("yyyy", 14, BirthDate)
Click to expand...
 
Thanks everyone for answering but I think I have to rephrase my question!

My users want to have two fields on their form where they can enter the
age bracket of the kids, let's say 12 and 14. And those numbers should
be used to get the list of kids who will have, at the date of a certain
activity, at least 12 years and at the most 14 (or 15 minus 1 day).

That's why I thought about [dateActivity]- x years etc.

Any new ideas? Is that doable?

Thanks a lot!
 
If the form is named TheForm and the text boxes on the form
are named txtAgeLow and txtAgeHigh:

SELECT kid
FROM table
WHERE [Activity Date] Between
DateAdd("yyyy", Forms!TheForm.txtAgeLow, BirthDate)
And DateAdd("yyyy", Forms!TheForm.txtAgeHigh, BirthDate)
--
Marsh
MVP [MS Access]

Thanks everyone for answering but I think I have to rephrase my question!

My users want to have two fields on their form where they can enter the
age bracket of the kids, let's say 12 and 14. And those numbers should
be used to get the list of kids who will have, at the date of a certain
activity, at least 12 years and at the most 14 (or 15 minus 1 day).

That's why I thought about [dateActivity]- x years etc.


Marshall said:
Jerome said:
I've a table of people (names, date of birth, etc) and one of activities
(name, genre, date, etc).

I'd like to select now the people corresponding to a certain age group
for a certain activity.

Like: List all the kids who, on the date of the activity, are at least
(x=)12 and at the most (y=)14 years old.

I've got to create a date formula I guess but I'm not really an expert
there. Something like Between(DateActivity - y years + 1 day) and
(DateActivity - x years) ... Can anybody help me expressing this in code?
Click to expand...


You don't really care about age, you can use the birthdate
instead.

SELECT kid
FROM table
WHERE [Activity Date] Between DateAdd("yyyy", 12, BirthDate)
And DateAdd("yyyy", 14, BirthDate)
Click to expand...
Click to expand...
 
Back
Top