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.
6 2315 Luuk 1,047
Expert 1GB
Something like: - select Name, Population, if(Population<130000,'1',IF(Population<170000,'2','3'))
-
from City
-
where CountryCode='USA';
(not tested with vba/access)
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). - 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: - 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
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.
NeoPa 32,556
Expert Mod 16PB
You should be aware that such links, though they work in Access, are not supported in the query design grid.
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.
NeoPa 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JingleBEV |
last post by:
Hi all,
I have a masked text control that would return a date in string format via
the mask property like:
"9/23/2004"
and i have a calendar control that would pop up when I click a command...
|
by: mg |
last post by:
When I click any date in System.Web.UI.WebControls.Calendar (C# WebForm), the SelectionChanged event handler fires. If I next click on the same date again, this event handler does not fire, so I...
|
by: anony |
last post by:
I'm sure I'm overlooking a built in method to convert a date to Month, day,
year - such as August 2, 2004 from a datetime. Is there a simple way to do
this, or must I do it myself?
Thanks.
|
by: Manny Chohan |
last post by:
Hi guys,
i am new to calendar control. i need to extract the selected date from the
calendar control however the code that i have retrieves the time default to
12:00 as well
is there any way i...
|
by: Chanaka |
last post by:
We are trying to pass the selected date from a .net calendar in a
querystring. We manage to display the selected date from the calendar in a
Lable like this :
protected void...
|
by: prabhat24singh |
last post by:
hi.
I correctly open Popo up Calendar But
I have some problem with pop up Calendar in ASP.NET 2.0 ..(with c#) when return date.I want to correct coding that return date from popup calendar to...
|
by: phpmel |
last post by:
Hi guys,
I have an asp calendar control, text box, calendar icon, eraser icon all within an update panel. The calendar works fine, postbacks are handled without the flickering of the screen etc....
|
by: macupryk |
last post by:
I have been looking for code to show a dropdownlist date for a person to select there date of birth without calendar.
I have the below code but if there is some code in Ajax Javascript I will use...
|
by: sparks |
last post by:
We are entering mm/dd/yyyy but some of the date fields only contain
years. Can a date be made to only take a year entry if that is all
that they have?
can it be made to take only 2008 or maybe...
|
by: piderobii |
last post by:
I can't get the selected date in the calendar to my textbox. Here is what I wrote in my Calendar.aspx.cs:
namespace Labb1
{
public partial class Calendar : System.Web.UI.Page
{
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |