Query and date selection


C

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,
 
Ad

Advertisements

G

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.
 
V

vanderghast

You can try a criteria under beginDate:

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


Vanderghast, Access MVP
 
J

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

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


John W. Vinson said:
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]


.
 
J

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.
 
Ad

Advertisements

C

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


John W. Vinson said:
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.
 
Ad

Advertisements

C

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


John W. Vinson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top