Query and date selection
in [Queries]
|
Prev: Show only this year
Next: Group By or MAX Question
From: John W. Vinson on 12 May 2010 19:09 On Wed, 12 May 2010 14:46:06 -0700, Chrissy <Chrissy(a)discussions.microsoft.com> wrote: >Thanks, John. > >I test on the StartDate, future only. So I entered... > >>Date() And (<DateSerial(Year([StartDate]),4,1) Or >DateSerial(Year([StartDate]),11,15)) > >This resulted, like what I was using, current year only. > >What do I do about all future dates falling in this range in any future >year? > >Thanks, If you don't reference the year in a date field it assumes the current year, so #11/15# will be 11/15/2010 if you run the query today, 11/15/2011 if you run it next year. My suggestion WILL WORK, if you try it. It will work this year, it will work next year, it will work in any year. -- John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 12 May 2010 19:44 Try a query like this: SELECT * FROM Reservations WHERE StartDate > DATE() AND FORMAT(StartDate, "mmdd") NOT BETWEEN "0401" AND "1114"; This works by excluding future dates outside the range rather than including those within it. Ken Sheridan Stafford, England Chrissy wrote: >Thanks, John. > >I test on the StartDate, future only. So I entered... > >>Date() And (<DateSerial(Year([StartDate]),4,1) Or >DateSerial(Year([StartDate]),11,15)) > >This resulted, like what I was using, current year only. > >What do I do about all future dates falling in this range in any future >year? > >Thanks, > >> >I test to see if a reservation begins between 11/15-3/31 of any year. >> > >[quoted text clipped - 16 lines] >> DateSerial(Year(Date()), Month([reservationdate]), Day([reservationdate])) >> BETWEEN #1/1# AND #3/31#; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: Chrissy on 12 May 2010 20:04 // If you don't reference the year in a date field it assumes the current year, so #11/15# will be 11/15/2010 if you run the query today, 11/15/2011 if you run it next year. // I inferred as much from your original post. // My suggestion WILL WORK, if you try it. It will work this year, it will work next year, it will work in any year. // If you refer to my StartDate as the object of your "not referencing", how? It is a reservation that must be day/month/year specific. So, if you do not mean that--why do I not return a StartDate of 1/05/11, when I do return a date of 11/20/10? Again, please not that my criteria for [StartDate] is ">Date() And (<DateSerial(Year([StartDate]),4,1) Or >DateSerial(Year([StartDate]),11,15)) " I follow the logic, it makes sense, but does not return as advertised. What could be the issue? -- Chrissy "John W. Vinson" wrote: > On Wed, 12 May 2010 14:46:06 -0700, Chrissy > <Chrissy(a)discussions.microsoft.com> wrote: > > >Thanks, John. > > > >I test on the StartDate, future only. So I entered... > > > >>Date() And (<DateSerial(Year([StartDate]),4,1) Or >DateSerial(Year([StartDate]),11,15)) > > > >This resulted, like what I was using, current year only. > > > >What do I do about all future dates falling in this range in any future > >year? > > > >Thanks, > > If you don't reference the year in a date field it assumes the current year, > so #11/15# will be 11/15/2010 if you run the query today, 11/15/2011 if you > run it next year. > > My suggestion WILL WORK, if you try it. It will work this year, it will work > next year, it will work in any year. > > -- > > John W. Vinson [MVP] > . >
From: Chrissy on 13 May 2010 10:07
Well, in the light of a new day it seems to work as you suggested. I must have been thrown by fixed "2010". So...thanks, once again. -- Chrissy "John W. Vinson" wrote: > On Wed, 12 May 2010 14:46:06 -0700, Chrissy > <Chrissy(a)discussions.microsoft.com> wrote: > > >Thanks, John. > > > >I test on the StartDate, future only. So I entered... > > > >>Date() And (<DateSerial(Year([StartDate]),4,1) Or >DateSerial(Year([StartDate]),11,15)) > > > >This resulted, like what I was using, current year only. > > > >What do I do about all future dates falling in this range in any future > >year? > > > >Thanks, > > If you don't reference the year in a date field it assumes the current year, > so #11/15# will be 11/15/2010 if you run the query today, 11/15/2011 if you > run it next year. > > My suggestion WILL WORK, if you try it. It will work this year, it will work > next year, it will work in any year. > > -- > > John W. Vinson [MVP] > . > |