Age calculation still a problem!

[Alec Green]

in my query I have a field DateofBirth and a calculated field Age: DateDiff
("yyyy",[DateofBirth],Now()) which works fine and returns the age correctly.
But I want to search for a range of ages and have been using the criteria
Between [Start Age] And [End Age] but this does not work!, I have also tried
running 2 different queries but to no avail any ideas please.

Alec Green

 

[John Viescas]

Alec-

First, you're not calculating the age correctly. See
http://www.mvps.org/access/datetime/date0001.htm for two ways to do that.

What do you mean by "does not work?" Does the query run but return the
wrong answer? Do you get an error? You might need to explicitly declare
the [Start Age] and [End Age] parameters. With the query in Design view,
choose Parameters from the Query menu and define them both as integers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Alec Green]

Alec-

First, you're not calculating the age correctly. See
http://www.mvps.org/access/datetime/date0001.htm for two ways to do that.

What do you mean by "does not work?" Does the query run but return the
wrong answer? Do you get an error? You might need to explicitly declare
the [Start Age] and [End Age] parameters. With the query in Design view,
choose Parameters from the Query menu and define them both as integers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

in my query I have a field DateofBirth and a calculated field Age: DateDiff
("yyyy",[DateofBirth],Now()) which works fine and returns the age correctly.
But I want to search for a range of ages and have been using the criteria
Between [Start Age] And [End Age] but this does not work!, I have also tried
running 2 different queries but to no avail any ideas please.

Alec Green

Thanks for your reply John, Yes the query does work and returns the
correct age. But when I add Between [Start Age] And [End Age] in the
criteria of the query - to search for any records with an age of 0 to
10, the results are incorrect (the query is pulling records with ages
other than 0-10 only)

Thank Again

 

[John Viescas]

Alec-

Please post the SQL from your query. Open the query in Design view and
choose SQL from the View menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

"John Viescas" <[email protected]> wrote in message

Alec-

First, you're not calculating the age correctly. See
http://www.mvps.org/access/datetime/date0001.htm for two ways to do that.

What do you mean by "does not work?" Does the query run but return the
wrong answer? Do you get an error? You might need to explicitly declare
the [Start Age] and [End Age] parameters. With the query in Design view,
choose Parameters from the Query menu and define them both as integers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

in my query I have a field DateofBirth and a calculated field Age: DateDiff
("yyyy",[DateofBirth],Now()) which works fine and returns the age correctly.
But I want to search for a range of ages and have been using the criteria
Between [Start Age] And [End Age] but this does not work!, I have also tried
running 2 different queries but to no avail any ideas please.

Alec Green

Thanks for your reply John, Yes the query does work and returns the
correct age. But when I add Between [Start Age] And [End Age] in the
criteria of the query - to search for any records with an age of 0 to
10, the results are incorrect (the query is pulling records with ages
other than 0-10 only)

Thank Again

 

[Alec Green]

Sorry John!, I have just understand the query parameter bit and it works
now.

Thanks Again

Alec

Alec-

Please post the SQL from your query. Open the query in Design view and
choose SQL from the View menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

"John Viescas" <[email protected]> wrote in message

Alec-

First, you're not calculating the age correctly. See
http://www.mvps.org/access/datetime/date0001.htm for two ways to do that.

What do you mean by "does not work?" Does the query run but return the
wrong answer? Do you get an error? You might need to explicitly declare
the [Start Age] and [End Age] parameters. With the query in Design view,
choose Parameters from the Query menu and define them both as integers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
in my query I have a field DateofBirth and a calculated field Age:
DateDiff
("yyyy",[DateofBirth],Now()) which works fine and returns the age
correctly.
But I want to search for a range of ages and have been using the criteria
Between [Start Age] And [End Age] but this does not work!, I have also
tried
running 2 different queries but to no avail any ideas please.

Alec Green

Thanks for your reply John, Yes the query does work and returns the
correct age. But when I add Between [Start Age] And [End Age] in the
criteria of the query - to search for any records with an age of 0 to
10, the results are incorrect (the query is pulling records with ages
other than 0-10 only)

Thank Again

 

[Alec Green]

Even Understood!

Sorry John!, I have just understand the query parameter bit and it works
now.

Thanks Again

Alec

Alec-

Please post the SQL from your query. Open the query in Design view and
choose SQL from the View menu.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

"John Viescas" <[email protected]> wrote in message

Alec-

First, you're not calculating the age correctly. See
http://www.mvps.org/access/datetime/date0001.htm for two ways to do that.

What do you mean by "does not work?" Does the query run but return the
wrong answer? Do you get an error? You might need to explicitly declare
the [Start Age] and [End Age] parameters. With the query in Design view,
choose Parameters from the Query menu and define them both as integers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
in my query I have a field DateofBirth and a calculated field Age:
DateDiff
("yyyy",[DateofBirth],Now()) which works fine and returns the age
correctly.
But I want to search for a range of ages and have been using the criteria
Between [Start Age] And [End Age] but this does not work!, I have also
tried
running 2 different queries but to no avail any ideas please.

Alec Green


Thanks for your reply John, Yes the query does work and returns the
correct age. But when I add Between [Start Age] And [End Age] in the
criteria of the query - to search for any records with an age of 0 to
10, the results are incorrect (the query is pulling records with ages
other than 0-10 only)

Thank Again