Calculate age

[Guest]

I have a database with a field for date of birth and I want to be able to
calculate age. What is the function / expression for this? I am a
sophisticated user but do not do visual basic so I would prefer a solution
without having to use VB.

 

[Allen Browne]

You can create an expression using DateDiff(), but you need to subract 1
from the result if the person has not yet had their birthday this year.

It does involve VBA, but see the discussion in this article:
http://allenbrowne.com/func-08.html

 

[Graham R Seach]

Mary Ann,

Abs(DateDiff("yyyy", dteDOB, dteDate2) - IIf(Format(dteDOB, "mmdd") <=
Format(dteDate2, "mmdd"), 0, 1))

You can use this in either a query or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Allen – Thank you for your response, especially the article explaining the
need to subtract 1. However, I can’t get the combination to work without the
VB elements so am going with Graham Seach’s suggestion.

 

[Guest]

Graham - Thank you for your reply – I’ve been away so apologise for my
delayed response.

I am delighted with the expression you offered – and I understand it!

I would like to check just a couple of things so I am absolutely sure what I
am doing.

If I use an absolute date in the expression, is it right to enclose it in
inverted commas e.g. “30/06/06â€

Because of the way I think, I have switched the Iif to use greater than and
wonder if you would check whether the result would still be correct. It does
seem to work.

Abs(DateDiff("yyyy",[DoB],"30/06/06")-IIf(Format([DoB],"mmdd")>Format("30/06/06","mmdd"),1,0))

With many thanks
Mary Ann

 

[Graham R Seach]

Mary Ann,

Allen's suggestion is basically the same thing; mine is just on one line.

Switching the IIf seems OK, but instead of enclosing a hard-coded date in
quotes, I'd be inclined to specify it with DateSerial (but then I'm just an
old pedant!).

Abs(DateDiff("yyyy",[DoB],DateSerial(2006,6,6))-IIf(Format([DoB],"mmdd")>Format(DateSerial(2006,6,6),"mmdd"),1,0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham - Thank you for your reply - I've been away so apologise for my
delayed response.

I am delighted with the expression you offered - and I understand it!

I would like to check just a couple of things so I am absolutely sure what
I
am doing.

If I use an absolute date in the expression, is it right to enclose it in
inverted commas e.g. "30/06/06"

Because of the way I think, I have switched the Iif to use greater than
and
wonder if you would check whether the result would still be correct. It
does
seem to work.

Abs(DateDiff("yyyy",[DoB],"30/06/06")-IIf(Format([DoB],"mmdd")>Format("30/06/06","mmdd"),1,0))

With many thanks
Mary Ann

Mary Ann,

Abs(DateDiff("yyyy", dteDOB, dteDate2) - IIf(Format(dteDOB, "mmdd") <=
Format(dteDate2, "mmdd"), 0, 1))

You can use this in either a query or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Thank you Graham. What is the benefit of using DateSerial in the expression
rather than the hard coded date? – I like to understand.

Mary Ann,

Allen's suggestion is basically the same thing; mine is just on one line.

Switching the IIf seems OK, but instead of enclosing a hard-coded date in
quotes, I'd be inclined to specify it with DateSerial (but then I'm just an
old pedant!).

Abs(DateDiff("yyyy",[DoB],DateSerial(2006,6,6))-IIf(Format([DoB],"mmdd")>Format(DateSerial(2006,6,6),"mmdd"),1,0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham - Thank you for your reply - I've been away so apologise for my
delayed response.

I am delighted with the expression you offered - and I understand it!

I would like to check just a couple of things so I am absolutely sure what
I
am doing.

If I use an absolute date in the expression, is it right to enclose it in
inverted commas e.g. "30/06/06"

Because of the way I think, I have switched the Iif to use greater than
and
wonder if you would check whether the result would still be correct. It
does
seem to work.

Abs(DateDiff("yyyy",[DoB],"30/06/06")-IIf(Format([DoB],"mmdd")>Format("30/06/06","mmdd"),1,0))

With many thanks
Mary Ann

Mary Ann,

Abs(DateDiff("yyyy", dteDOB, dteDate2) - IIf(Format(dteDOB, "mmdd") <=
Format(dteDate2, "mmdd"), 0, 1))

You can use this in either a query or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a database with a field for date of birth and I want to be able
to
calculate age. What is the function / expression for this? I am a
sophisticated user but do not do visual basic so I would prefer a
solution
without having to use VB.

 

[Graham R Seach]

Mary Ann,

Like I said, I'm just an old pendant!

I don't like defining dates as strings, because they ain't strings. At a
pinch (rarely though that be), I might define a date explicitly using the
hash (#30/6/2006#), but because I use dates a lot in queries, and given I
use UK date format, I often need to format the date in the correct format
using Format(#7/6/2006#, "mm/dd/yyyy")), to account for the fact that Jet
considers dates to be in US format, by default. I prefer to use DateSerial
because I find it more explicit, unambiguously defining it as a date
datatype, and I don't have to be concerned about the US/UK format
difference.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you Graham. What is the benefit of using DateSerial in the
expression
rather than the hard coded date? - I like to understand.

Mary Ann,

Allen's suggestion is basically the same thing; mine is just on one line.

Switching the IIf seems OK, but instead of enclosing a hard-coded date in
quotes, I'd be inclined to specify it with DateSerial (but then I'm just
an
old pedant!).

Abs(DateDiff("yyyy",[DoB],DateSerial(2006,6,6))-IIf(Format([DoB],"mmdd")>Format(DateSerial(2006,6,6),"mmdd"),1,0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham - Thank you for your reply - I've been away so apologise for my
delayed response.

I am delighted with the expression you offered - and I understand it!

I would like to check just a couple of things so I am absolutely sure
what
I
am doing.

If I use an absolute date in the expression, is it right to enclose it
in
inverted commas e.g. "30/06/06"

Because of the way I think, I have switched the Iif to use greater than
and
wonder if you would check whether the result would still be correct.
It
does
seem to work.

Abs(DateDiff("yyyy",[DoB],"30/06/06")-IIf(Format([DoB],"mmdd")>Format("30/06/06","mmdd"),1,0))

With many thanks
Mary Ann


:

Mary Ann,

Abs(DateDiff("yyyy", dteDOB, dteDate2) - IIf(Format(dteDOB, "mmdd") <=
Format(dteDate2, "mmdd"), 0, 1))

You can use this in either a query or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a database with a field for date of birth and I want to be
able
to
calculate age. What is the function / expression for this? I am a
sophisticated user but do not do visual basic so I would prefer a
solution
without having to use VB.

 

[Neil Sunderland]

Like I said, I'm just an old pendant!

Actually, you didn't

 

[Duane Hookom]

Actually Graham did, just not in this thread.

 

[Guest]

Thanks Graham for the benefit of all that experience - whether pedantic or
not, I appreciate.
Regards
Mary Ann

Mary Ann,

Like I said, I'm just an old pendant!

I don't like defining dates as strings, because they ain't strings. At a
pinch (rarely though that be), I might define a date explicitly using the
hash (#30/6/2006#), but because I use dates a lot in queries, and given I
use UK date format, I often need to format the date in the correct format
using Format(#7/6/2006#, "mm/dd/yyyy")), to account for the fact that Jet
considers dates to be in US format, by default. I prefer to use DateSerial
because I find it more explicit, unambiguously defining it as a date
datatype, and I don't have to be concerned about the US/UK format
difference.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you Graham. What is the benefit of using DateSerial in the
expression
rather than the hard coded date? - I like to understand.

Mary Ann,

Allen's suggestion is basically the same thing; mine is just on one line.

Switching the IIf seems OK, but instead of enclosing a hard-coded date in
quotes, I'd be inclined to specify it with DateSerial (but then I'm just
an
old pedant!).

Abs(DateDiff("yyyy",[DoB],DateSerial(2006,6,6))-IIf(Format([DoB],"mmdd")>Format(DateSerial(2006,6,6),"mmdd"),1,0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham - Thank you for your reply - I've been away so apologise for my
delayed response.

I am delighted with the expression you offered - and I understand it!

I would like to check just a couple of things so I am absolutely sure
what
I
am doing.

If I use an absolute date in the expression, is it right to enclose it
in
inverted commas e.g. "30/06/06"

Because of the way I think, I have switched the Iif to use greater than
and
wonder if you would check whether the result would still be correct.
It
does
seem to work.

Abs(DateDiff("yyyy",[DoB],"30/06/06")-IIf(Format([DoB],"mmdd")>Format("30/06/06","mmdd"),1,0))

With many thanks
Mary Ann


:

Mary Ann,

Abs(DateDiff("yyyy", dteDOB, dteDate2) - IIf(Format(dteDOB, "mmdd") <=
Format(dteDate2, "mmdd"), 0, 1))

You can use this in either a query or VBA.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a database with a field for date of birth and I want to be
able
to
calculate age. What is the function / expression for this? I am a
sophisticated user but do not do visual basic so I would prefer a
solution
without having to use VB.

 

[Graham R Seach]

<<Actually Graham did, just not in this thread.>>
Alzheimers!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------