# Thread: Age Range Report / Query

1. Registered User
Join Date
Aug 2004
Posts
364

## Unanswered: Age Range Report / Query

I have a query with IDnumber and Age. I have 30 records with IDnumber and Ages. How can I do an age-range report or query? E.g. number of people aged 20-25, 26-30, 31-35, 26-40 etc?

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
You Could Either use SQL Like this

SELECT DISTINCT IIf([AGE] Mod 4<>0,Left(([AGE]/4)+1,1),Left(([AGE]/4),1)) AS [Band], Switch([Band]="1","0 - 4 Year",[Band]="2","5-8 Years") AS AgeBand
FROM YourTable;

Expanding the Switch Statements for all your Bands or you could Write a Select Case Function in VBA And use this

HTH

Dave

3. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Copy the following code and put it into a new module:

Code:
```Public Function AgeRange(lngAge As Long) As String

Select Case lngAge

Case Is < 13
AgeRange = "0 - 12"
Case Is < 18
AgeRange = "13 - 17"
Case Is < 26
AgeRange = "18 - 25"
Case Is < 31
AgeRange = "26 - 30"
Case Is < 41
AgeRange = "31 - 40"
Case Is < 51
AgeRange = "41 - 50"
Case Is < 61
AgeRange = "51 - 60"
Case Is < 71
AgeRange = "61 - 70"
Case Is < 81
AgeRange = "71 - 80"
Case Is < 91
AgeRange = "81 - 90"
Case Else
AgeRange = "91+"
End Select

End Function```
Modify the ranges that you want. Then in your query add a field like:

Range:AgeRange([Age])

That will call the AgeRange function for each Age field and return the range. When I do Age Range reports I create a lookup table with each range and the sort order for each, because the age ranges never sort properly without a forced sort order.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•