G
Guest
i need a query that would help me calculate homany kids are in age range from
1-9 years old
1-9 years old
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Do you have a table that has birthdates? If so, open the Query Builder, and
add the table to the window. Drag the * down to the Field: row. In the next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your date
field.
Good Luck!
add the table to the window. Drag the * down to the Field: row. In the next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your date
field.
Good Luck!
D
Douglas J. Steele
Unfortunately, the calculation you show for Age is incorrect: it'll mistate
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Chaim said:Do you have a table that has birthdates? If so, open the Query Builder,
and
add the table to the window. Drag the * down to the Field: row. In the
next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your
date
field.
Good Luck!
--
Chaim
Jessica said:i need a query that would help me calculate homany kids are in age range
from
1-9 years old
G
Guest
I don't know about this. I ran into a problem before with using DateDiff
where it calculated the year difference, not the actual age. For example, Jan
1 and Dec. 31 of the same year generated the same "age" for a test on days.
But clearly, on Jun 1, one will be a year older than the other.
Be sure and test carefully!
Simple solution if you are doing this only one time:
Put the correct start and end dates as criteria in your query, and use the
Totals button to do a Count on the number of kids.
Here's a Function I wrote for getting an age in years.
Public Function AgeInYears(dtborn As Date, dt2 As Date) As Integer
'The problem is that DateDiff("YYYY",dt1,dt2) only tests for whether a
'year is crossed, not an actual duration in years.
'Plus, there is the matter of leap years.
Dim yy As Integer
Dim mm1 As Integer, mm2 As Integer
Dim dd1 As Integer, dd2 As Integer
yy = DateDiff("YYYY", dtborn, dt2)
mm1 = Month(dtborn)
mm2 = Month(dt2)
'Example1, born 6.20.1999, today 6.28.2005. Age should be 9 years.
'Example2 born 7.3.99. Age should be 8 years, though almost 9.
'Example3 born 6.30.99. Age should be 8 years, though almost 9.
If mm1 > mm2 Then 'see example 2
yy = yy - 1
Else 'example 1 & 3
If mm1 = mm2 Then
dd1 = Day(dtborn)
dd2 = Day(dt2)
If dd1 > dd2 Then 'example 3 applies
yy = yy - 1
End If
End If
End If
AgeInYears = yy
End Function
Maybe there is a way to do this without all this fuss. If so, I'd like to
know!!
- Phil Freihofner
where it calculated the year difference, not the actual age. For example, Jan
1 and Dec. 31 of the same year generated the same "age" for a test on days.
But clearly, on Jun 1, one will be a year older than the other.
Be sure and test carefully!
Simple solution if you are doing this only one time:
Put the correct start and end dates as criteria in your query, and use the
Totals button to do a Count on the number of kids.
Here's a Function I wrote for getting an age in years.
Public Function AgeInYears(dtborn As Date, dt2 As Date) As Integer
'The problem is that DateDiff("YYYY",dt1,dt2) only tests for whether a
'year is crossed, not an actual duration in years.
'Plus, there is the matter of leap years.
Dim yy As Integer
Dim mm1 As Integer, mm2 As Integer
Dim dd1 As Integer, dd2 As Integer
yy = DateDiff("YYYY", dtborn, dt2)
mm1 = Month(dtborn)
mm2 = Month(dt2)
'Example1, born 6.20.1999, today 6.28.2005. Age should be 9 years.
'Example2 born 7.3.99. Age should be 8 years, though almost 9.
'Example3 born 6.30.99. Age should be 8 years, though almost 9.
If mm1 > mm2 Then 'see example 2
yy = yy - 1
Else 'example 1 & 3
If mm1 = mm2 Then
dd1 = Day(dtborn)
dd2 = Day(dt2)
If dd1 > dd2 Then 'example 3 applies
yy = yy - 1
End If
End If
End If
AgeInYears = yy
End Function
Maybe there is a way to do this without all this fuss. If so, I'd like to
know!!
- Phil Freihofner
Douglas J. Steele said:Unfortunately, the calculation you show for Age is incorrect: it'll mistate
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Chaim said:Do you have a table that has birthdates? If so, open the Query Builder,
and
add the table to the window. Drag the * down to the Field: row. In the
next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your
date
field.
Good Luck!
--
Chaim
Jessica said:i need a query that would help me calculate homany kids are in age range
from
1-9 years old
D
Douglas J. Steele
The whole point of the extra code I included (the "- IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)" part) was to ensure that the age
is correct. That extra bit is the equivalent of all your additional code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"mmdd") < Format([date1], "mmdd"), 1, 0)" part) was to ensure that the age
is correct. That extra bit is the equivalent of all your additional code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Phil F said:I don't know about this. I ran into a problem before with using DateDiff
where it calculated the year difference, not the actual age. For example,
Jan
1 and Dec. 31 of the same year generated the same "age" for a test on
days.
But clearly, on Jun 1, one will be a year older than the other.
Be sure and test carefully!
Simple solution if you are doing this only one time:
Put the correct start and end dates as criteria in your query, and use the
Totals button to do a Count on the number of kids.
Here's a Function I wrote for getting an age in years.
Public Function AgeInYears(dtborn As Date, dt2 As Date) As Integer
'The problem is that DateDiff("YYYY",dt1,dt2) only tests for whether a
'year is crossed, not an actual duration in years.
'Plus, there is the matter of leap years.
Dim yy As Integer
Dim mm1 As Integer, mm2 As Integer
Dim dd1 As Integer, dd2 As Integer
yy = DateDiff("YYYY", dtborn, dt2)
mm1 = Month(dtborn)
mm2 = Month(dt2)
'Example1, born 6.20.1999, today 6.28.2005. Age should be 9 years.
'Example2 born 7.3.99. Age should be 8 years, though almost 9.
'Example3 born 6.30.99. Age should be 8 years, though almost 9.
If mm1 > mm2 Then 'see example 2
yy = yy - 1
Else 'example 1 & 3
If mm1 = mm2 Then
dd1 = Day(dtborn)
dd2 = Day(dt2)
If dd1 > dd2 Then 'example 3 applies
yy = yy - 1
End If
End If
End If
AgeInYears = yy
End Function
Maybe there is a way to do this without all this fuss. If so, I'd like to
know!!
- Phil Freihofner
Douglas J. Steele said:Unfortunately, the calculation you show for Age is incorrect: it'll
mistate
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Chaim said:Do you have a table that has birthdates? If so, open the Query Builder,
and
add the table to the window. Drag the * down to the Field: row. In the
next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your
date
field.
Good Luck!
--
Chaim
:
i need a query that would help me calculate homany kids are in age
range
from
1-9 years old
G
Guest
Nice! When I first looked at your function, I wasn't following it, but now I
see.
- Phil Freihofner
see.
- Phil Freihofner
Douglas J. Steele said:The whole point of the extra code I included (the "- IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)" part) was to ensure that the age
is correct. That extra bit is the equivalent of all your additional code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Phil F said:I don't know about this. I ran into a problem before with using DateDiff
where it calculated the year difference, not the actual age. For example,
Jan
1 and Dec. 31 of the same year generated the same "age" for a test on
days.
But clearly, on Jun 1, one will be a year older than the other.
Be sure and test carefully!
Simple solution if you are doing this only one time:
Put the correct start and end dates as criteria in your query, and use the
Totals button to do a Count on the number of kids.
Here's a Function I wrote for getting an age in years.
Public Function AgeInYears(dtborn As Date, dt2 As Date) As Integer
'The problem is that DateDiff("YYYY",dt1,dt2) only tests for whether a
'year is crossed, not an actual duration in years.
'Plus, there is the matter of leap years.
Dim yy As Integer
Dim mm1 As Integer, mm2 As Integer
Dim dd1 As Integer, dd2 As Integer
yy = DateDiff("YYYY", dtborn, dt2)
mm1 = Month(dtborn)
mm2 = Month(dt2)
'Example1, born 6.20.1999, today 6.28.2005. Age should be 9 years.
'Example2 born 7.3.99. Age should be 8 years, though almost 9.
'Example3 born 6.30.99. Age should be 8 years, though almost 9.
If mm1 > mm2 Then 'see example 2
yy = yy - 1
Else 'example 1 & 3
If mm1 = mm2 Then
dd1 = Day(dtborn)
dd2 = Day(dt2)
If dd1 > dd2 Then 'example 3 applies
yy = yy - 1
End If
End If
End If
AgeInYears = yy
End Function
Maybe there is a way to do this without all this fuss. If so, I'd like to
know!!
- Phil Freihofner
Douglas J. Steele said:Unfortunately, the calculation you show for Age is incorrect: it'll
mistate
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Do you have a table that has birthdates? If so, open the Query Builder,
and
add the table to the window. Drag the * down to the Field: row. In the
next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your
date
field.
Good Luck!
--
Chaim
:
i need a query that would help me calculate homany kids are in age
range
from
1-9 years old
G
Guest
I have a table that has a field with birthdates and one that has their ages.
What i need to find out homany kind are in the ages range from 1-9.
What i need to find out homany kind are in the ages range from 1-9.
Chaim said:Do you have a table that has birthdates? If so, open the Query Builder, and
add the table to the window. Drag the * down to the Field: row. In the next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your date
field.
Good Luck!
--
Chaim
Jessica said:i need a query that would help me calculate homany kids are in age range from
1-9 years old
G
Guest
I have a table with fields birthdates and one with ages, i need to run a
query that tells me what kids are 1 to 9 years old
query that tells me what kids are 1 to 9 years old
Douglas J. Steele said:Unfortunately, the calculation you show for Age is incorrect: it'll mistate
the age of all children who haven't yet had their birthday.
Instead, use: Age: DateDiff("yyyy", [date1], Date()) - IIf(Format(Date(),
"mmdd") < Format([date1], "mmdd"), 1, 0)
(watch out for word-wrap there: that should be all on one line)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Chaim said:Do you have a table that has birthdates? If so, open the Query Builder,
and
add the table to the window. Drag the * down to the Field: row. In the
next
column, enter:
Field row: Age: DateDiff("yyyy", [date1], Date())
Show: Unchecked (unless you want to see the ages)
Criteria: Between 1 and 9
Note: date1 is simply the name that I used in my test table. You don't
provide table structure information, so I don't know what you call your
date
field.
Good Luck!
--
Chaim
Jessica said:i need a query that would help me calculate homany kids are in age range
from
1-9 years old
J
John Vinson
I have a table with fields birthdates and one with ages, i need to run a
query that tells me what kids are 1 to 9 years old
Put a calculated field into a Query by typing:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0)
Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M); Group By Age and select the * "field", and
use the Count totals operator on it.
John W. Vinson[MVP]