Between in a query

  • Thread starter Thread starter Tom R.
  • Start date Start date
T

Tom R.

Hello!

I have a field [Fødselsdato1] (birthdate, short date) and I am interested in
finding out all those who are 50 to 60 years.
I tried Year(Date())-50 and Year(Date())-60 and found those that are 50 and
60, but not all those from 51 to 59 years.

Can someone please help me?
 
Hello!

I have a field [Fødselsdato1] (birthdate, short date) and I am interested in
finding out all those who are 50 to 60 years.
I tried  Year(Date())-50 and Year(Date())-60 and found those that are 50and
60, but not all those from 51 to 59 years.

Can someone please help me?
Click to expand...

Try this

Between Year(Date())-50 and Year(Date())-60


This will give you the 50s and 60s and everything between.
Ron
 
Using just the year of a birthday is about 50% accurate. Now it is January.
Frodo was born in May. He will be 50 in may, but you will show him as 50
before he actually is. You need a routine to determine the actual age of a
person.

Here is a function that does that:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function


Now, to use this in a query:

Select Age([Birthdate], Date) As PersonsAge WHERE PersonsAge BETWEEN 50 AND 60
 
I can't believe this would work if you are setting a criteria against a date
field.
Year(Date())-50 = (2008 - 50) = 1958
CDate(1958) = 5/11/1905

If you compared the Year(Date())-50 to the year of birth, this should work.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Ron2006 said:
Hello!

I have a field [Fødselsdato1] (birthdate, short date) and I am interested in
finding out all those who are 50 to 60 years.
I tried Year(Date())-50 and Year(Date())-60 and found those that are 50 and
60, but not all those from 51 to 59 years.

Can someone please help me?
Click to expand...

Try this

Between Year(Date())-50 and Year(Date())-60


This will give you the 50s and 60s and everything between.
Ron
Click to expand...
 
The most efficient way to do this is

Field: [Fødselsdato1]
Criteria: BETWEEN DateAdd("yyyy",-60,Date()) and DateAdd("yyyy",-50,Date())

That should show anyone who is between 50 and 60 as of today's date. If you
want all those who will be between 50 and 60 anytime during the current
year,then the criteria will be slightly different. I believe that would be

BETWEEN DateSerial(Year(Date())-60,1,1) and
DateSerial(Year(Date())-50,12,31)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top