If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to display an age in query based on Datediff expression in a f
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")) |
#2
|
|||
|
|||
How to display an age in query based on Datediff expression in a f
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")) -- Dave Hargis, Microsoft Access MVP "Natahn" wrote: 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")) |
#3
|
|||
|
|||
How to display an age in query based on Datediff expression in
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" wrote: 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")) -- Dave Hargis, Microsoft Access MVP "Natahn" wrote: 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")) |
Thread Tools | |
Display Modes | |
|
|