Calculating Age in Query

  • Thread starter Thread starter Alec Green
  • Start date Start date
A

Alec Green

I need a quick way of calculating a persons age, so I can search on it. I
have a field called DateOfBirth, which holds the person Date of Birth. I
have used the following expression to calculate the age:

Age: DateDiff("y",[DateOfBirth],Date())/365

which appears to work, but if I put a search criteria on this (Between
[Start Age] And [End Age]) the results are incorrect.

I am I going about this the wrong way???

Regards

Alec
 
You need two queries the first calculates the number of
days from "Date of Birth" to "Todays Date". In a new
Query: one field will calculate the number of days
Example: AGEINDAYS:[Today Date-DOB) This will return a
whole number. A second field will calculate the age.
Example: AGE:[AGEINDAYS]/365] this will return the age.
You can not search for the age in this query. A second
query is used to search for your data plus the age.
 
Alex,
I made a typeing error in field "AGEINDAYS" please use
[Date()]-[DOB]


You need two queries the first calculates the number of
days from "Date of Birth" to "Todays Date". In a new
Query: one field will calculate the number of days
Example: AGEINDAYS:[Date()]-[DOB] This will return a
whole number. A second field will calculate the age.
Example: AGE:[AGEINDAYS]/365] this will return the age.
You can not search for the age in this query. A second
query is used to search for your data plus the age.


-----Original Message-----
You need two queries the first calculates the number of
days from "Date of Birth" to "Todays Date". In a new
Query: one field will calculate the number of days
Example: AGEINDAYS:[Today Date-DOB) This will return a
whole number. A second field will calculate the age.
Example: AGE:[AGEINDAYS]/365] this will return the age.
You can not search for the age in this query. A second
query is used to search for your data plus the age.



-----Original Message-----
I need a quick way of calculating a persons age, so I can search on it. I
have a field called DateOfBirth, which holds the person Date of Birth. I
have used the following expression to calculate the age:

Age: DateDiff("y",[DateOfBirth],Date())/365

which appears to work, but if I put a search criteria
Click to expand...
on
this (Between
[Start Age] And [End Age]) the results are incorrect.

I am I going about this the wrong way???

Regards

Alec


.
Click to expand...
.
Click to expand...
 
Using the simple AGE calculation you display, you would have SQL roughly like:

SELECT ...
FROM ...
WHERE (Date()-DOB /365) Between 1 and 10

You do realize that the age you calculate is an approximation and is not totally
accurate.

You could also use the following
DateDiff("d",DOB,Date())/365 Between 1 and 10

A MORE accurate method calculating Age in terms of years:

DateDiff("yyyy",DOB,Date()) + CInt(Format(DOB,"MMDD") > FORMAT(Date(),"MMDD"))
Between 1 and 10


Denny said:
Alex,
I made a typeing error in field "AGEINDAYS" please use
[Date()]-[DOB]

You need two queries the first calculates the number of
days from "Date of Birth" to "Todays Date". In a new
Query: one field will calculate the number of days
Example: AGEINDAYS:[Date()]-[DOB] This will return a
whole number. A second field will calculate the age.
Example: AGE:[AGEINDAYS]/365] this will return the age.
You can not search for the age in this query. A second
query is used to search for your data plus the age.
-----Original Message-----
You need two queries the first calculates the number of
days from "Date of Birth" to "Todays Date". In a new
Query: one field will calculate the number of days
Example: AGEINDAYS:[Today Date-DOB) This will return a
whole number. A second field will calculate the age.
Example: AGE:[AGEINDAYS]/365] this will return the age.
You can not search for the age in this query. A second
query is used to search for your data plus the age.



-----Original Message-----
I need a quick way of calculating a persons age, so I can search on it. I
have a field called DateOfBirth, which holds the person Date of Birth. I
have used the following expression to calculate the age:

Age: DateDiff("y",[DateOfBirth],Date())/365

which appears to work, but if I put a search criteria
Click to expand...
on
this (Between
[Start Age] And [End Age]) the results are incorrect.

I am I going about this the wrong way???

Regards

Alec


.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top