Date to age parameter query

  • Thread starter Thread starter Zachary
  • Start date Start date
Z

Zachary

Hello all,

Does anyone know how to convert a birthday DATE\TIME
field, mm/dd/yy, to a age? Then use a Between...AND,
parameter query to search for people between ages??? say
between 5-10, or whatever. The query would display all the
records of the kids whose birthdates fall between these
ages.

Thank you

Zach
 
PARAMETERS [Start Age] Long, [End Age] Long;
SELECT [FirstName] & " " & [LastName] AS FullName, YourTable.BirthDate,
DateDiff("yyyy",[BirthDate],Date())-IIf(Format([BirthDate],"mmdd")>Format(Da
te(),"mmdd"),1,0) AS Age
FROM YourTable
WHERE (((YourTable.BirthDate) Is Not Null) AND
((DateDiff("yyyy",[BirthDate],Date())-IIf(Format([BirthDate],"mmdd")>Format(
Date(),"mmdd"),1,0)) Between [Start Age] And [End Age]));

You will be prompted for the ages each time you run the query.

Change YourTable to your actual table name, and BirthDate to the name of the
field containing the birth date.
 
I just gave you an example.
Add whatever fields you need.
Easiest way is to just copy and paste this into a query SQL view window.
(Paste it over any existing code.)
Manually change the Table and Field names as needed.
Then open the query in Design View and drag whatever additional fields you
need onto the query grid.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Zach said:
Ok, great.
So I would create a query from the table, then add this to
the sql view??? and if I made a form to enter this
parameter into i would put the forms!formname!textbox
where the start age and end age are in the statment right??
If I have more fields than the first and last name in the
table do i need to add them to this statement as well???
thank you very much for your help
i appreciate it greatly
Zach
-----Original Message-----
PARAMETERS [Start Age] Long, [End Age] Long;
SELECT [FirstName] & " " & [LastName] AS FullName, YourTable.BirthDate,
DateDiff("yyyy",[BirthDate],Date())-IIf(Format
Click to expand...
([BirthDate],"mmdd")>Format(Da
te(),"mmdd"),1,0) AS Age
FROM YourTable
WHERE (((YourTable.BirthDate) Is Not Null) AND
((DateDiff("yyyy",[BirthDate],Date())-IIf(Format ([BirthDate],"mmdd")>Format(
Date(),"mmdd"),1,0)) Between [Start Age] And [End Age]));

You will be prompted for the ages each time you run the query.

Change YourTable to your actual table name, and BirthDate to the name of the
field containing the birth date.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Zachary said:
Hello all,

Does anyone know how to convert a birthday DATE\TIME
field, mm/dd/yy, to a age? Then use a Between...AND,
parameter query to search for people between ages??? say
between 5-10, or whatever. The query would display all the
records of the kids whose birthdates fall between these
ages.

Thank you

Zach
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top