Using Between...And Criteria with Ages

[Guest]

I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Phil]

Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil

 

[Guest]

As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil

I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Phil]

Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.

As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:

Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:

I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Guest]

Yes it is. Thanks.

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblIndividuals.DateofBirth) Is Not Null) AND
((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True) AND
((Int((Date()-[DateofBirth])/365.25)) Between [Start Age] And [End Age]));

Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.

As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:

Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:


I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Guest]

Have thought of another option, but cant get it working either, is to put the
age range onto a form, then generate the report using those details. This is
how i have that SQL in the report query. The form simply has two text boxes
(AgeStart & AgeFinish) and a button to open the report in preview form. This
returns everybody, but may be another way of working if we can figure it.

Cheers

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True)
AND
(((Int((Date()-[DateofBirth])/365.25))>=[forms]![frmreports].[agestart])<=[forms]![frmreports].[agefinish]));


_____________________________________________-

Yes it is. Thanks.

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblIndividuals.DateofBirth) Is Not Null) AND
((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True) AND
((Int((Date()-[DateofBirth])/365.25)) Between [Start Age] And [End Age]));

Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.

As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:


Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:


I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Phil]

Question. Forget the between part for a minute, let the query run
without criteria, and put AGE as the first field, and sort by just AGE.
Do you get the results you would expect?



Have thought of another option, but cant get it working either, is to
put the

age range onto a form, then generate the report using those details. This is
how i have that SQL in the report query. The form simply has two text boxes
(AgeStart & AgeFinish) and a button to open the report in preview form. This
returns everybody, but may be another way of working if we can figure it.

Cheers

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True)
AND
(((Int((Date()-[DateofBirth])/365.25))>=[forms]![frmreports].[agestart])<=[forms]![frmreports].[agefinish]));


_____________________________________________-


:

Yes it is. Thanks.

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblIndividuals.DateofBirth) Is Not Null) AND
((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True) AND
((Int((Date()-[DateofBirth])/365.25)) Between [Start Age] And [End Age]));


:

Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.



As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:



Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:



I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Guest]

Hey, good point.

Yes I do though, and all the ages are shown in the correct order youngest to
oldest.

Question. Forget the between part for a minute, let the query run
without criteria, and put AGE as the first field, and sort by just AGE.
Do you get the results you would expect?



Have thought of another option, but cant get it working either, is to
put the

age range onto a form, then generate the report using those details. This is
how i have that SQL in the report query. The form simply has two text boxes
(AgeStart & AgeFinish) and a button to open the report in preview form. This
returns everybody, but may be another way of working if we can figure it.

Cheers

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True)
AND
(((Int((Date()-[DateofBirth])/365.25))>=[forms]![frmreports].[agestart])<=[forms]![frmreports].[agefinish]));


_____________________________________________-


:

Yes it is. Thanks.

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblIndividuals.DateofBirth) Is Not Null) AND
((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True) AND
((Int((Date()-[DateofBirth])/365.25)) Between [Start Age] And [End Age]));


:


Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.



As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:



Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:



I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.

 

[Phil]

I got nothing more to help you with without seeing the thing in person.
how big is the database?

IF it is under a gig, ship it via www.yousendit.com. My email address is
phil (at) nhs-inc.com. I will look at it for you and send you an
functional SQL to add back in.

Phil

Hey, good point.

Yes I do though, and all the ages are shown in the correct order youngest to
oldest.

:

Question. Forget the between part for a minute, let the query run
without criteria, and put AGE as the first field, and sort by just AGE.
Do you get the results you would expect?



Have thought of another option, but cant get it working either, is to
put the

age range onto a form, then generate the report using those details. This is
how i have that SQL in the report query. The form simply has two text boxes
(AgeStart & AgeFinish) and a button to open the report in preview form. This
returns everybody, but may be another way of working if we can figure it.

Cheers

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age
FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID
= tblIndividuals.FamilyID
WHERE (((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True)
AND
(((Int((Date()-[DateofBirth])/365.25))>=[forms]![frmreports].[agestart])<=[forms]![frmreports].[agefinish]));


_____________________________________________-


:



Yes it is. Thanks.

SELECT tblIndividuals.DateofBirth, tblIndividuals.IndividualID,
tblIndividuals.FamilyID, tblIndividuals.Name, tblIndividuals.DiffLastName,
tblIndividuals.[Male/Female], tblFamilyDetails.FamilyName,
tblFamilyDetails.Current, tblIndividuals.Current,
Int((Date()-[DateofBirth])/365.25) AS Age

FROM tblFamilyDetails INNER JOIN tblIndividuals ON tblFamilyDetails.FamilyID

= tblIndividuals.FamilyID
WHERE (((tblIndividuals.DateofBirth) Is Not Null) AND
((tblFamilyDetails.Current)=True) AND ((tblIndividuals.Current)=True) AND
((Int((Date()-[DateofBirth])/365.25)) Between [Start Age] And [End Age]));


:



Weird. Post the SQL of the query.
Maybe I can spot waht's wrong.




As far as I can tell it is. I am in the query, and have set the field
property to General Number Format, but nothing. I have also set the
DateOfBirth format to ShortDate.
When I just use from 2 to 2, I get the correct result and it ignors the
20's, 32, 42 etc.

Cheers.

:




Is everything in a numeric form?
Sounds like it is treating yoour age like text, where
2,21,22,
comes before 3,31,33, Just like

A,AB,ABC
comes before
B,BA,BAC.

Make sure your field types for Age are numeric.

Phil


azza wrote:




I am wanting to list poeple between ages that I specify. I have the
birthdates entered, and am using "Age: Int((Date()-[DateOfBirth])/365.25)" to
get the current age.

I am then using the Criteria in a query "Between [Enter Start Age] And
[Enter End Age]" to ask for the ages to show in the results. For example
poeple between ages 2 & 4.

However it is giving me results with any age that has the number 2, 3 or 4
in the age as the first or second digit.

Any ideas as to how I can make it only shoe me results for poeple 2, 3 or 4
(Or whatever age range I specify.

Thanks.