Need help making ranges

[CandaceB]

I guess I should start by saying I'm a beginner in Access. I have enough knowledge to be a menace. Not a threat or a danger, just enough to stir up trouble.



I have a membership database. In this I want to group the children by their ages and gender. Ages can range from 1 month to 17+ years. I used this statement

IIf(DateDiff("m",[BDay],Date())<12,DateDiff("m",[BDay],Date()),DateDiff("yyyy",[BDay],Date())+Int(Format(Date(),"mmdd")<Format([BDay],"mmdd"))) to get the age. field name:[Age]

In a separate text box I used this statement IIf(DateDiff("m",[BDay],Date())<12,"mth"," yr") to indicate whether the age was in months or years. field name:[Mth Yr] So far (I think) this will work for me.

What I would like to do is create the following age ranges to group the kids:

1-12 months, group 1
2-4 years, group 2
5-7 years, group 3
8-10 years, group 4
11-13 years, group 5
14-17 years, group 6
18+ years, group 7.

I have a column in my query that calculates the age. I also have a column for the month year text. I made separate columns in the query for the groups, but I don't think this will work when I want to group them on the report. I could do an iif statement, but I need to have a 2 part expression. Like if [Age] is <12 and [Mth Yr] = "mth" then put a 1. Every number below 12 could be the age in months or years. I don't want to group 3 month old kids with 3 year old kids.

Please help.I've been playing with this for 2 days.
Candace

[June7]

For 1-12 months, year will be 1. Year 1 would be group 1.

IIf(DateDiff("m",[BDay],Date())<12, 1, calc for older ages)

[CandaceB]

Thank you for your response. Does the "calc for older ages" mean to use the datediff function to calculate the other ages in years? How will I get the other group ranges? Group 2 includes kids ages 2-4 years. If this is the formula I would use, where would I use it? I tried to put it in the column on the query by typing: New:IIf(DateDiff("m",[BDay],Date())<12, 1, calc for older ages). I got a caution box stating that the expression contains invalid syntax. When I click OK, the for is highlighted. I'm missing something.

[June7]

'calc for older ages' means 'put your formula here'.

I was just being too lazy to copy/paste what you show in your original post.

Then use the Age field to calculate groups.

Switch([Age]=1,1, [Age]<5,2, [Age]<8,3, [Age]<11,4, [Age]<14,5, [Age]<18,6, [Age]>17,7)

[CandaceB]

Thank you thank you! Never seen the switch function in my searches. Now I have a little more knowledge to add to my menacing. Your my new hero June7. You ARE a Super Moderator.

[June7]

Choose() is another versatile function you might get familiar with.