how do i get the age for an output of a query knowing the DOB?

[Tech Express]

I have a DOB on the database.
Table = Members
Field = DOB

I seen this as a formula - “AgeCalcField: AgeInYears([DOBField], Date())†-


Within my query I do this
I click the field box
and coded an expression as listed here and get a syntax error:

“AgeCalcField: AgeInYears([MembersOB], Date())â€


Can someone help in getting the age on my query working?

Thanks a lot - Russ @ scaninc.org
--

 

[Jerry Whittle]

Hi Russ,

The AgeInYears is a custom function that needs to be in a module to work.
That is unless you have Access 2007 and it includes such a function. I just
checked 2003 and AgeInYears is not there.

Here's what probably is a similar function that I've used many times:

http://www.mvps.org/access/datetime/date0001.htm

 

[John W. Vinson]

I have a DOB on the database.
Table = Members
Field = DOB

I seen this as a formula - “AgeCalcField: AgeInYears([DOBField], Date())” -


Within my query I do this
I click the field box
and coded an expression as listed here and get a syntax error:

“AgeCalcField: AgeInYears([MembersOB], Date())”

If 2007 gives you hassles with putting in VBA code, you can do the calculation
directly in a query:

AgeCalcField: DateDiff("yyyy", [DOBfield], Date()) - IIF(Format([DOBfield],
"mmdd") > Format(Date(), "mmdd"), 1, 0)

 

[Rob DSO Llandrillo]

another way to do this that i use is:
Age: Instr((#27/01/2009#-[date_of_birth])/365.25)

this will give you their age as at the date you enter, if you want this to
always be todays date i think you can substitute the date with Now()

Rob
--
Rob
Data Systems Officer
Coleg Llandrillo
North Wales

Hi Russ,

The AgeInYears is a custom function that needs to be in a module to work.
That is unless you have Access 2007 and it includes such a function. I just
checked 2003 and AgeInYears is not there.

Here's what probably is a similar function that I've used many times:

http://www.mvps.org/access/datetime/date0001.htm
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

I have a DOB on the database.
Table = Members
Field = DOB

I seen this as a formula - “AgeCalcField: AgeInYears([DOBField], Date())†-


Within my query I do this
I click the field box
and coded an expression as listed here and get a syntax error:

“AgeCalcField: AgeInYears([MembersOB], Date())â€


Can someone help in getting the age on my query working?

Thanks a lot - Russ @ scaninc.org