By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,035
Something like:
Expand|Select|Wrap|Line Numbers
  1. select Name, Population, if(Population<130000,'1',IF(Population<170000,'2','3')) 
  2. from City 
  3. 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
  1. Public Function fAge(ByVal TheDOB) As Integer
  2.     Dim YearsDiff As Integer
  3.     Dim BirthdayThisYear As Date
  4.     If Not IsNull(TheDOB) Then
  5.         BirthdayThisYear = DateSerial(Year(Date), Month(TheDOB), Day(TheDOB))
  6.         YearsDiff = DateDiff("yyyy", TheDOB, BirthdayThisYear)
  7.         If BirthdayThisYear > Date Then
  8.             YearsDiff = YearsDiff - 1
  9.         End If
  10.     End If
  11.     fAge = YearsDiff
  12. 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
  1. 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.

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,
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

Post your reply

Sign in to post your reply or Sign up for a free account.