How to display an age in query based on Datediff expression in a f

  • Thread starter Thread starter Natahn
  • Start date Start date
N

Natahn

Help! I am kind of a novice at Access and need to display an age in a query,
based on the Datediff formula I have in a form (.mbd), but all I get are
zeroes! I saw the post from 6/10 did not have a conclusion.

here is the SQL for the query as I have it now (showing only "0" for age):

SELECT [FBC Community Database].Prefix, [FBC Community Database].FirstName,
[FBC Community Database].LastName, [FBC Community Database].DateJoined, [FBC
Community Database].DateBaptized, [FBC Community Database].Age
FROM [FBC Community Database];

here is the expression I have in my form for the "age" field (based on
birthdate):

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))
 
First I see a field named Age. Get rid of it. You should never store
calculated values in your database when you have the information available to
do the calculation. It wastes space and time, and has a high probability of
being wrong. Since a person ages every year, this will obviously be
incorrect after the person's next birthday. Also, change the Now() function
to date. You don't need a time component for this unless you want to know to
the second how old a person is.

Here is what you need. It is called a Calculated field.

SELECT [FBC Community Database].Prefix, [FBC Community Database].FirstName,
[FBC Community Database].LastName, [FBC Community Database].DateJoined, [FBC
Community Database].DateBaptized, DateDiff("yyyy",[Birthdate],
Date())+Int(Format(Date(),"mmdd")<Format([Birthdate],"mmdd")) AS Age
FROM [FBC Community Database];
here is the expression I have in my form for the "age" field (based on
birthdate):

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))
Click to expand...
--
Dave Hargis, Microsoft Access MVP


Natahn said:
Help! I am kind of a novice at Access and need to display an age in a query,
based on the Datediff formula I have in a form (.mbd), but all I get are
zeroes! I saw the post from 6/10 did not have a conclusion.

here is the SQL for the query as I have it now (showing only "0" for age):

SELECT [FBC Community Database].Prefix, [FBC Community Database].FirstName,
[FBC Community Database].LastName, [FBC Community Database].DateJoined, [FBC
Community Database].DateBaptized, [FBC Community Database].Age
FROM [FBC Community Database];

here is the expression I have in my form for the "age" field (based on
birthdate):

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))
Click to expand...
 
Thank you very much, your answer was on target and helpful. Just FYI, I
actually simply copied and pasted the Datediff expression from a Microsoft
office online support page. I had searched for an expression to display an
age from a birthdate and that is the formula they supplied. You may want to
have them correct that formula (it was not a discussion forum page).

Klatuu said:
First I see a field named Age. Get rid of it. You should never store
calculated values in your database when you have the information available to
do the calculation. It wastes space and time, and has a high probability of
being wrong. Since a person ages every year, this will obviously be
incorrect after the person's next birthday. Also, change the Now() function
to date. You don't need a time component for this unless you want to know to
the second how old a person is.

Here is what you need. It is called a Calculated field.

SELECT [FBC Community Database].Prefix, [FBC Community Database].FirstName,
[FBC Community Database].LastName, [FBC Community Database].DateJoined, [FBC
Community Database].DateBaptized, DateDiff("yyyy",[Birthdate],
Date())+Int(Format(Date(),"mmdd")<Format([Birthdate],"mmdd")) AS Age
FROM [FBC Community Database];
here is the expression I have in my form for the "age" field (based on
birthdate):

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))
Click to expand...
--
Dave Hargis, Microsoft Access MVP


Natahn said:
Help! I am kind of a novice at Access and need to display an age in a query,
based on the Datediff formula I have in a form (.mbd), but all I get are
zeroes! I saw the post from 6/10 did not have a conclusion.

here is the SQL for the query as I have it now (showing only "0" for age):

SELECT [FBC Community Database].Prefix, [FBC Community Database].FirstName,
[FBC Community Database].LastName, [FBC Community Database].DateJoined, [FBC
Community Database].DateBaptized, [FBC Community Database].Age
FROM [FBC Community Database];

here is the expression I have in my form for the "age" field (based on
birthdate):

=DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))
Click to expand...
Click to expand...
 
Back
Top