Calculating an age from a DoB in an Access table

[tkq]

I have a normal table with customer information

I have a DoB field and I want to have an automatic calculation that will show
the age of the customer in another field next to it called Age

Could you please help me with this

Thanx all

[jahoobob via AccessMonster.com]

Age: (Now()-[Birth Date])/365.25

tkq wrote:

I have a normal table with customer information

I have a DoB field and I want to have an automatic calculation that will show
the age of the customer in another field next to it called Age

Could you please help me with this

Thanx all

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[tkq]

I tried what you suggested but this error message comes up

"Invalid SQL syntax - cannont use multiple columns in a column-level CHECK
constraint."

Do you know how to overcome this

[fredg]

On Mon, 03 Apr 2006 13:05:31 GMT, tkq wrote:

I have a normal table with customer information

I have a DoB field and I want to have an automatic calculation that will show
the age of the customer in another field next to it called Age

Could you please help me with this

Thanx all

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Bob Quintal]

"tkq" <u20428@uwe> wrote in news:5e3a073204b88@uwe:

I tried what you suggested but this error message comes up

"Invalid SQL syntax - cannont use multiple columns in a
column-level CHECK constraint."

Do you know how to overcome this

You are trying to store the data at the table level. You need to
enter the formula in any of a query, a form or a report. It makes
no sense to store it in the table because it will need to be
recalculated every time you open the table, or any query based on
that table.

So to overcome this, remove the field 'age' from the table and put
it wherever you use to display the age.

--
Bob Quintal

PA is y I've altered my email address.

[CDMAPoster]

tkq wrote:

I have a normal table with customer information

I have a DoB field and I want to have an automatic calculation that will show
the age of the customer in another field next to it called Age

Could you please help me with this

Thanx all

In a query:

SELECT Int(Format(Date(),"yyyy.mmdd") -
Format([BirthDate],"yyyy.mmdd")) AS AgeNow FROM tblBirthdays;

Note that the double quotes above can be converted to single quotes
when a dynamic SQL string is being created.

As a Control Source:

=Int(Format(Date(),"yyyy.mmdd") - Format([BirthDate],"yyyy.mmdd"))

Note that Null values for [BirthDate] aren't handled here. For your
example, replace AgeNow with Age and replace [BirthDate] with your DoB
field name. I have used a similar technique for calculating elapsed
months using "mm.dd" with the Format command as part of the
calculation.

James A. Fortune
CD********@FortuneJames.com

[Saran]

To extend this a little bit.

Can have a query some thing like

SELECT tblBirthdays.*, Int(Format(Date(),"yyyy.mmdd") -
Format([tblBirthdays.BirthDate],"yyyy.mmdd")) AS AgeNow FROM
tblBirthdays

You will get everything in your table plus a calculated Age column.

-Saran

[tkq via AccessMonster.com]

Saran wrote:

To extend this a little bit.

Can have a query some thing like
Where in the query would I write this. That is the only thing i'm not sure of

SELECT tblBirthdays.*, Int(Format(Date(),"yyyy.mmdd") -
Format([tblBirthdays.BirthDate],"yyyy.mmdd")) AS AgeNow FROM
tblBirthdays

You will get everything in your table plus a calculated Age column.

-Saran

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[CDMAPoster]

tkq via AccessMonster.com wrote:

Saran wrote:

To extend this a little bit.

Can have a query some thing like

Where in the query would I write this. That is the only thing i'm not sure of

There's a SQL View for queries. Paste the text into the SQL View
window. I usually put a semicolon at the end of the SQL text.

James A. Fortune
CD********@FortuneJames.com

[tkq via AccessMonster.com]

CD********@FortuneJames.com wrote:

>To extend this a little bit.
>
>Can have a query some thing like

Where in the query would I write this. That is the only thing i'm not sure of

There's a SQL View for queries. Paste the text into the SQL View
window. I usually put a semicolon at the end of the SQL text.

James A. Fortune
CD********@FortuneJames.com

Thanks very much, I've got it to work now
Thanks to all who helped aswell
Much Appreciated

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[stuartjbray]

Another way of doing this is to divide by the real number of days in a
year then round down. Thus the difference between date 1 and date 2...

YearsDiff = Int(([d2]-[d1])/365.242)

MonthDiff = ([d2]-[d1])/(365.242/12) Mod 12