Query and date selection

[Chrissy]

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,

 

[ghetto_banjo]

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.

 

[vanderghast]

You can try a criteria under beginDate:

< DateSerial( Year(beginDate), 4, 1) OR > DateSerial( Year(beginDate),
11, 15)


Vanderghast, Access MVP

 

[John W. Vinson]

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#;

 

[Chrissy]

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

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]


.

 

[John W. Vinson]

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.

 

[Chrissy]

//
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

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.

 

[Chrissy]

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

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.