Age Ranges

[luke jones]

Hey guys, have 2 issues I can't seem to find the answer to.

1. I have combo boxes on a form that filter a table via a query. One of the fields contains clients ages. I need a combo box to display age groups (under 18, 19-25, 26-35 etc.) and filter the table accordingly but can't seem to find a way to group the individual ages into these categories.

2. Optional ComboBox Filtering

Thanks in advance for any help.

[Delerna]

1
You need a calculated field in the query that the form is bound to

select iif(age<18,"Under 18",
          iif(age>17 and age<26,"19-25",
          iif(age>25 and age<36,"26-35","etc"))),otherFields
From theTable

Now you can use the selected value in the combobox as a criteria for that field.


A neater way if your age groupings allow for it would be to do something like

take the integer of the age divided by 5 to represent the age group
...
...
13/5= 2
14/5= 2
15/5= 3
16/5= 3
17/5= 3
18/5= 3
19/5= 3
20/5= 4
21/5= 4
...
...

Now your combobox needs two fields from a table or query

Group  Display
3       15-19
4       20-24
5       25-19
 

and you use the group from the combo
as a criteria for the int(age/5) field

[NeoPa]

If you build your ComboBox to have multiple columns then you can build a filter from the values of the respective lower- and upper-end columns using the format :

[FIELD] Between {LowerVal} And {UpperVal}