Calculating age, given DOB & current date

[Paul Hyett]

Hi, I'm a complete novice on Access, but with a lot of experience on
Excel.

How would I go about the above?

 

[Douglas J. Steele]

The usual formula is:

DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

This is necessary because DateDiff is a little too literal in how it
calculates differences: to it, there's 1 year between 31 Dec, 2005 and 1
Jan, 2006. That's why you have to add the second part: to subtract one from
what DateDiff returns if the birthday hasn't happened yet this year.

Now, how were you planning on using this?

Hopefully, you know that you do not store Age in a table.

Your options are to create a query, and add the Age calculation as a
computed field in the query, or use it as the Control Source for a text box
on your form.

 

[Paul Hyett]

In microsoft.public.access.forms on Sat, 18 Feb 2006, Douglas J. Steele
wrote :

The usual formula is:

DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

This is necessary because DateDiff is a little too literal in how it
calculates differences: to it, there's 1 year between 31 Dec, 2005 and 1
Jan, 2006. That's why you have to add the second part: to subtract one from
what DateDiff returns if the birthday hasn't happened yet this year.

Now, how were you planning on using this?

Just to calculate ages for a list of people

Hopefully, you know that you do not store Age in a table.

Not unless you *enjoy* updating a whole load of entries every day.

Your options are to create a query, and add the Age calculation as a
computed field in the query, or use it as the Control Source for a text box
on your form.

I don't know how to do the latter, so I'll experiment with the former.

Thanks.

 

[Paul Hyett]

In microsoft.public.access.forms on Sun, 19 Feb 2006, Paul Hyett wrote :

DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

Your options are to create a query, and add the Age calculation as a
computed field in the query, or use it as the Control Source for a text box
on your form.

I don't know how to do the latter, so I'll experiment with the former.

Yes, that was just want I wanted - thanks again.