# Thread: age as of 31/8/2007

1. Registered User
Join Date
Mar 2003
Posts
225

## Unanswered: age as of 31/8/2007

how do i calculate someones age on 31/8/2007?

i have a field with DOB in fo in it..

i have been playing with datediff but cant get there

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select name
, year(#2007-08-31#)
-year(DOB)
-iif(month(#2007-08-31#) > month(DOB),0
,iif(month(#2007-08-31#) < month(DOB),1
,iif(  day(#2007-08-31#) < day(DOB),1,0 ))) as age
from yourtable```

3. (Making Your Life Easy)
Join Date
Feb 2004
Location
New Zealand
Posts
1,482
hes some code I use

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
If Years = 0 then Years = ""
Age =  Years & "." & Months - Temp

End if

End Function```
in a Query

Age: age([dob],date())

it will return

year.months

I use it in my HR database to work out when
a person can have sick days

only after 6 months .6
then every year 1.

must copy code to a module

4. Registered User
Join Date
Mar 2003
Posts
225
many thanks worked a treat

#### Posting Permissions

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