1. Registered User
Join Date
Sep 2004
Posts
29

Hi all,

I am trying to evaluate a persons age in the month of june of each year. For example I need to know how old was a person in June 2005 and how old this person is going to be in June 2006.

I have managed to do this query, but there is some hardcoding in there. I need this query to be dynamic and based on DATE() function.

SELECT DDN.DOB, Year(Date())-Year(dob)-IIf(Month(Date())>Month(dob),0,IIf(Month(Date())<M onth(dob),1,IIf(Day(Date())<Day(dob),1,0))) AS AgeActuel, Year(#6/30/2005#)-Year(dob)-IIf(Month(#6/30/2005#)>Month(dob),0,IIf(Month(#6/30/2005#)<Month(dob),1,IIf(Day(#6/30/2005#)<Day(dob),1,0))) AS Juin2005, Year(#6/30/2006#)-Year(dob)-IIf(Month(#6/30/2006#)>Month(dob),0,IIf(Month(#6/30/2006#)<Month(dob),1,IIf(Day(#6/30/2006#)<Day(dob),1,0))) AS Juin2006
FROM DDN;

Thanks

RC

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi

I don't piddle about with SQL for this. It makes for ugly, difficult to follow code. It is also easy to make a mistake. Use VB (e.g. http://support.microsoft.com/?scid=k...d=2509&sid=202) instead and your SQL becomes dead easy

HTH

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
BTW - if you know how old someone will be in June2005... does that not leave with you with the answer for 2006?

4. Registered User
Join Date
Sep 2004
Posts
29
Thanks for your input...the function is nice.

I still need to know how old a person is on each end of june. The reason behind this is to know if a competitor for a sports meet matches the age criteria on june end of each year

So for example, if today is Jan 15 2006, i need to know how old a competitor was on end june 2005. If we are say July 15 2006, i need to know how hold is the same competitor on end june 2006.

I know this can be done with IIF in SQL...I have hardcoded the date values there but it's not practical. I could also store the limit dates values in the database but it's not what i'd want to do.

Cheers

RC

Join Date
Feb 2004
Location
New Zealand
Posts
1,475
Here is a Function I wrote

Code:
```Function Age(Birth_Date,End_Date)
'***************************************
' Works out the age of to the month
'
'***************************************
Dim Months
Dim Years
Dim Temp
'Check if no error
If IsNull(Birth_Date) or Birth_Date ="" Then
Age=0.0
else

Months = DateDiff("m", CDate(Birth_Date), End_Date)
Years = Int(Months / 12)
Temp = Years * 12
Age =  Years & "." & (Months - Temp)
End if

End Function```
Paste into a Module

then in a query

Bla : age([startdate],[enddate])

if you want age now

Bla : age([startdate],date())

and

Bla : age([startdate],#01/06/2007#)

I use in the payroll program I wrote

how long been working here for end of fin year
Last edited by myle; 06-30-06 at 00:22.

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by duaner
So for example, if today is Jan 15 2006, i need to know how old a competitor was on end june 2005. If we are say July 15 2006, i need to know how hold is the same competitor on end june 2006.
Hi RC

But that is my point - whether you are looking on Jan 15 2006 or July 15 2006 the competitor will be the same age in June 2006 - one year older than in June 2005. Each and every time.

You can do it in an Iif but I would seriously avoid. myle has what is effectively an adaption of the function I posted with an additional "on this date" criteria. Call this for #01/06/2005# and again for #01/06/2006#

HTH

Join Date
Feb 2004
Location
New Zealand
Posts
1,475

But I do agree with his IIF if can get messy inside a iif()

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi Myle

HTH = Hope This Helps

I know you didn't adapt it. I put in what is effectively because it essentially does the same as the function I linked to except that you've added the ability to provide the date for which you want the "age" rather than always using today. I was trying to stress that these were (to all intents and purposes) the same (rather than competing) solutions just that the code differed a little and you had added the parameter duaner would need.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•