how do i calculate age?

[Pharcyde]

Hi, I need t calculate how many people <21, between 21 & 25, and >25.

The format of the dates I have is dd/mm/yyyy


So how do I calculate the ages??

P.S - I know its a stupid quiestion - I'm struggling abit today

 

[Brianwarnock]

The following code works

Option Compare Database

Public Function fGetAge(dtDOB As Variant, dtDOL As Variant) As Integer


Dim dtBDay As Date
If Not IsDate(dtDOB) Or Not IsDate(dtDOL) Then Exit Function
dtBDay = DateSerial(Year(dtDOL), Month(dtDOB), Day(dtDOB))
fGetAge = DateDiff("yyyy", dtDOB, dtDOL) + (dtBDay > dtDOL)

End Function

Brian

 

[Mile-O]

There have been loads of Age formulas posted on this forum - just do a search.

 

[Pharcyde]

Thanks a lot. Should've searched in the first place hey!?

The only thing is, i can only see DateDiff functions that use Date() .

I would like to use a static date: 04/09/2003 - so how would I use that instead??

 

[Mile-O]

Changed Brian's function a little:

Public Function fGetAge(dtDOB As Variant) As Integer

    Const dtDOL = #04/09/2003#

    Dim dtBDay As Date 
    If Not IsDate(dtDOB) Or Not IsDate(dtDOL) Then Exit Function

    dtBDay = DateSerial(Year(dtDOL), Month(dtDOB), Day(dtDOB)) 
    fGetAge = DateDiff("yyyy", dtDOB, dtDOL) + (dtBDay > dtDOL) 

End Function

Don't know if you wanted #04/09/2003# or #09/04/2003# - remember it's the US format.

 

[Brianwarnock]

Just back from a meeting and picked this up, thanks for taking it up Mile, but just to clarify, or confuse, the issue although the date of the constant has to be in US format the date being fed into Datdiff from your databsae can be in accordance with your system settings , in my case UK, at least thats what my trial and error suggests



Brian

 

[Mile-O]

I know - it's just in case the date was either way round and a cut and past job was done.

 

[Brianwarnock]

I knew you knew Mile I mentioned it incase somebody whodidn't thought that they needed to carry dates in their Db in US format

Brian