434,762 Members | 1,845 Online
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

# automate date of birth to age groups in calendar year

 P: 2 I have a list of club members ranging in age from 5 years to 62 years of age. They are grouped into several age groups ie. under 9's, under 12's, under 15's, under 17's, under 20's, and Seniors. The question is how to automate my access db so that every time a club member reaches the next age group, ie. an 11 year old turns 12 in say 2013, he/she gets automatically slotted into the next age group. Currently I have to go through my list of members and manually change their age group when their dob hits the current year. May 19 '12 #1
6 Replies

 Expert 100+ P: 1,035 Something like: Expand|Select|Wrap|Line Numbers select Name, Population, if(Population<130000,'1',IF(Population<170000,'2','3'))  from City  where CountryCode='USA'; (not tested with vba/access) May 19 '12 #2

 Expert Mod 2.5K+ P: 2,545 I would suggest that you need to calculate the age of the person concerned, then group the age into the bandings you mention. If you calculate the age you should never have to manually assign groupings the way you are doing at present. I use a custom VBA function which calculates the age of a person as at the current date. To use the function you first need to copy the code below and place it in any public code module (you'll need to create one if you do not have any public modules at present). Expand|Select|Wrap|Line Numbers Public Function fAge(ByVal TheDOB) As Integer     Dim YearsDiff As Integer     Dim BirthdayThisYear As Date     If Not IsNull(TheDOB) Then         BirthdayThisYear = DateSerial(Year(Date), Month(TheDOB), Day(TheDOB))         YearsDiff = DateDiff("yyyy", TheDOB, BirthdayThisYear)         If BirthdayThisYear > Date Then             YearsDiff = YearsDiff - 1         End If     End If     fAge = YearsDiff End Function With this (or any equivalent function) you can then refer to a person's age in any query by using a calculated field. In SQL terms this would just be: Expand|Select|Wrap|Line Numbers SELECT YourFirstField, YourNextField,..., fAge([YourDOBField]) as Age, ..., FROM YourTable NB By default Access databases do not allow VBA code to run - in Access 2007 and later you need to change the trust centre settings to allow macros to run. -Stewart May 19 '12 #3

 Expert Mod 10K+ P: 12,366 Create an age group table with a min and max for the range. Then you can join to it on the age where it's between the min and max. May 20 '12 #4

 Expert Mod 15k+ P: 31,489 You should be aware that such links, though they work in Access, are not supported in the query design grid. May 20 '12 #5

 P: 2 Thanks Guys, will dedicate a little more time in a day or two to think about this, I'm not exactly an expert on db so everything takes time. But I will get back to you soon. Thanks again, Noxi. May 21 '12 #6

 Expert Mod 15k+ P: 31,489 That's not a problem Noxi. NB. I wasn't trying to put you off the idea of the more complex linking, just warning you that you shouldn't expect to be able to see the query in Design View after you've made that change. You'll have to work in SQL View after that. May 21 '12 #7