less than a year

# Thread: less than a year

1. Senior Member
Join Date
Dec 1969
Posts
1,030

## less than a year

I need to pull records less that a year old to a range.<BR><BR>For example the user inputs they want records 0 (meeaning less that 1 year old) to 4 meaning 4 years old how would you write the code for records less than a year old? I know the 4 year part it&#039;s the less than that is causing the grief.<BR><BR>Thanks

2. Senior Member
Join Date
Dec 1969
Posts
1,030

## Let me rephrase

I&#039;ve just been told I didn&#039;t understand the premise.<BR><BR>The user has a form that they can enter up to 5 age ranges into.<BR><BR>The input from the form would look like<BR><BR>fd1 = 0 td1 = 4<BR><BR>Meaning ages from todays date up to 4 years old from todays date or 2/4/2004 back to 2/4/2000.<BR><BR>The user could also enter<BR><BR>fd1 = 0 td1 = 0 <BR><BR>meaning they only need the records of people less than 1 year old.<BR><BR>Is this making sense? I can&#039;t figure out how to code the less than a year records.<BR><BR>Thanks<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Let me rephrase

so you&#039;re storing the age, not the date of birth?<BR><BR>don&#039;t do that. store the DOB, calculate the age, and use the DateDiff() function to achieve your search

4. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Let me rephrase

I misread. you ARE storing DOB, aren&#039;t you?<BR><BR>skip the first bit and use DateDiff() to get people in a given range of ages.<BR><BR>DateDiff(mm, BirthDate, GetDate) &#060; 12

5. Senior Member
Join Date
Dec 1969
Posts
1,030

## RE: Let me rephrase

Yep. Storing the dob.<BR><BR>OK I get the leass than a year but I was using DateAdd to get a range greater than 1 year. You said DateDiff.<BR><BR>I&#039;m doing something like this to get the end range:<BR><BR>dtNow = date()<BR>td1 = 4<BR>td1c = DateAdd("yyyy", -td1, dtNow)<BR><BR>And then running the query based on the resulting date.<BR><BR>Not a good idea?

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## RE: less than a year

Access:<BR><BR>SELECT * FROM table<BR>WHERE when &#062; DateAdd( &#039;yyyy&#039;, -1, Date() )<BR><BR>SQL Server:<BR><BR>SELECT * FROM table<BR>WHERE when &#062; DateAdd( Year, -1, GetDate() )<BR><BR>With the understanding that with SQL Server the "less than a year" will be exact to the second. (And could be, in Access, if you just use TIME() in place of DATE().)<BR><BR>***********<BR><BR>But wait a second!<BR><BR>If the user wants records from 0 to 4, then why do you *NEED* to make a test for 0 at all????<BR><BR>These queries will get all records where "when" is any time in last 4 years:<BR><BR>Access:<BR><BR>SELECT * FROM table<BR>WHERE when &#062; DateAdd( &#039;yyyy&#039;, -4, Date() )<BR><BR>SQL Server:<BR><BR>SELECT * FROM table<BR>WHERE when &#062; DateAdd( Year, -4, GetDate() )<BR><BR><BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
1,030

## Okay but what if

The user puts 0 in both form fields? Or 4 in both.<BR><BR>Just wanting ages less than 1 only or 4 year olds only.

8. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Let me rephrase

potentially a good idea, but there&#039;s often more than one way to go about these things...

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## This makes NO SENSE...

Would you allow an entry of<BR> fd1=4 td1=4<BR>????<BR><BR>If so *WHAT DOES THAT MEAN*???<BR><BR>Does it mean "from 4 years old to one-day-less-than-5-years-old"??<BR><BR>If so, then okay, it makes sense.<BR><BR>But then how is 0,0 different than 4,4 or different than 72,72???<BR><BR>Let&#039;s see...<BR><BR>&#060;%<BR>fd1 = ...<BR>td1 = ...<BR>&#039; simple enough...works if fd1 is zero (doesn&#039;t change date() value)<BR>enddate = DateAdd("yyyy", fd1, Date())<BR>&#039; example: td1 is 4. Today is 2/4/2004.<BR>&#039; The DateAdd gives you 2/4/1999<BR>&#039; add 1 to that and you get 2/5/1999<BR>startdate = ( DateAdd("yyyy", td1+1, Date()) ) + 1<BR><BR>clause = " DOB BETWEEN #" & startdate & "# AND #" & enddate & "#"<BR><BR>Response.Write clause & "&#060;HR&#062;"<BR>%&#062;<BR><BR>And if I did it right, that will indeed show you<BR> DOB BETWEEN #2/5/1999# AND #2/4/2004#<BR><BR>All people whose age is 0 through 4. (If their DOB is 2/4/1999 they are 5 years old and shouldn&#039;t be selected.)<BR><BR>Is *THIS* close to right????<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
96,118