If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query and date selection
I test to see if a reservation begins between 11/15-3/31 of any year.
So far I have come up with, for the reservation begin date: Between #11/15/2009# And #12/31/2009# Or Between #1/1/2009# And #3/31/2009# This, of course, only tests for 2009. Can I use a year wildcard? Is there a better way? Thanks in advance, -- Chrissy |
#2
|
|||
|
|||
Query and date selection
Add Another Field to your query, something like this:
monthdayfield: Format([ReservationDate], "mm/dd") And then for the criteria: ="11/15" or ="03/31" I think that will do the trick. |
#3
|
|||
|
|||
Query and date selection
You can try a criteria under beginDate:
DateSerial( Year(beginDate), 4, 1) OR DateSerial( Year(beginDate), 11, 15) Vanderghast, Access MVP "Chrissy" wrote in message ... I test to see if a reservation begins between 11/15-3/31 of any year. So far I have come up with, for the reservation begin date: Between #11/15/2009# And #12/31/2009# Or Between #1/1/2009# And #3/31/2009# This, of course, only tests for 2009. Can I use a year wildcard? Is there a better way? Thanks in advance, -- Chrissy |
#4
|
|||
|
|||
Query and date selection
On Wed, 12 May 2010 12:43:01 -0700, Chrissy
wrote: I test to see if a reservation begins between 11/15-3/31 of any year. So far I have come up with, for the reservation begin date: Between #11/15/2009# And #12/31/2009# Or Between #1/1/2009# And #3/31/2009# This, of course, only tests for 2009. Can I use a year wildcard? Is there a better way? Thanks in advance, Dates aren't stored as strings, so wildcards aren't appropriate. I'd suggest using the DateSerial function to map the date to this year's date: WHERE DateSerial(Year(Date()), Month([reservationdate]),Day([reservationdate])) BETWEEN #11/15# AND #12/31# OR DateSerial(Year(Date()), Month([reservationdate]), Day([reservationdate])) BETWEEN #1/1# AND #3/31#; -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Query and date selection
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, -- Chrissy "John W. Vinson" wrote: On Wed, 12 May 2010 12:43:01 -0700, Chrissy wrote: I test to see if a reservation begins between 11/15-3/31 of any year. So far I have come up with, for the reservation begin date: Between #11/15/2009# And #12/31/2009# Or Between #1/1/2009# And #3/31/2009# This, of course, only tests for 2009. Can I use a year wildcard? Is there a better way? Thanks in advance, Dates aren't stored as strings, so wildcards aren't appropriate. I'd suggest using the DateSerial function to map the date to this year's date: WHERE DateSerial(Year(Date()), Month([reservationdate]),Day([reservationdate])) BETWEEN #11/15# AND #12/31# OR DateSerial(Year(Date()), Month([reservationdate]), Day([reservationdate])) BETWEEN #1/1# AND #3/31#; -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
Query and date selection
On Wed, 12 May 2010 14:46:06 -0700, 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, 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] |
#7
|
|||
|
|||
Query and date selection
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/For...eries/201005/1 |
#8
|
|||
|
|||
Query and date selection
//
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 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] . |
#9
|
|||
|
|||
Query and date selection
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 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] . |
Thread Tools | |
Display Modes | |
|
|