number of people within age renge?

[baddy]

I need some help here. I have a table which stores DateOf Birth . I used DateDiff to get the age of persons but what I need is a number of persons within certain age range (0-15, 15-30, 30-50, 50>). How should I get that?

 

[jnixon]

I typically use a formula to convert what I want to know, and then create filters around that. For example, it's good that you're using DateDiff to figure out the age, but you need to create another query on top of that to make a condition. This condition could be linked to a table or form, or a form controlled by a table if you wish; we'll call it "Age Prompt" as an example. The condition in your new query should look like this:

[B]Criteria:[/B] Between [Forms]![Age Prompt]![AgeRange1] And [Forms]![Age Prompt]![AgeRange2]

 

[ScottGem]

I need some help here. I have a table which stores DateOf Birth . I used DateDiff to get the age of persons but what I need is a number of persons within certain age range (0-15, 15-30, 30-50, 50>). How should I get that?

I would create a custom function to return the age range:

Public Function AgeRange(dteDOB As Date) As String

Dim intAge as Integer

intAge = DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

Select Case intAge
Case <16
AgeRange = "0-15"

Case >15 AND < 31
AgeRange = "16-30"

...

End Select
End Function

Create query that with the first column:

Ages: AgeRange([DOB])

and the second column the PK field. Group on the range and Count the PK.

 

[baddy]

Thanks,

I’l try both of your suggestions and see what suits me better (If I menage to get it working)

 

[baddy]

No, it didn’t work. In fact I need to desplay user by gender and within age categories. Should look like this:

range1 range2 range 3 range4
M ------ ------ ------ ------
F ------ ------ ------ ------

The DOB is stored in table Users. Please help....

 

[ScottGem]

What didn't work? The code I listed should have allowed you to create a query with the age range for each person. If you can't group by that column, then save that as an interim query and create a new query based on the interim query that groups on the range. However, this will give you a layout like this:

ageRange...Gender...Count
0-15..........M..........10
0-15..........F...........12

If you want a layout like you describe, take the interim query and use a crosstab.