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.c om
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:5e3a073204 b88@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(Dat e(),"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********@Fort uneJames.com

[Saran]

To extend this a little bit.

Can have a query some thing like

SELECT tblBirthdays.*, Int(Format(Date (),"yyyy.mmdd" ) -
Format([tblBirthdays.Bi rthDate],"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.Bi rthDate],"yyyy.mmdd" )) AS AgeNow FROM
tblBirthdays

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

-Saran

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

[CDMAPoster]

tkq via AccessMonster.c om 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********@Fort uneJames.com

[tkq via AccessMonster.com]

CD********@Fort uneJames.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********@For tuneJames.com

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

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