Prev: Show only this year
Next: Group By or MAX Question
From: Chrissy on 12 May 2010 15:43
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
From: ghetto_banjo on 12 May 2010 15:55
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.
From: vanderghast on 12 May 2010 16:01
You can try a criteria under beginDate:

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


Vanderghast, Access MVP


"Chrissy" <Chrissy(a)discussions.microsoft.com> wrote in message
news:322406D4-1507-4B5F-98B8-B9D024D2CE75(a)microsoft.com...
>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

From: John W. Vinson on 12 May 2010 16:26
On Wed, 12 May 2010 12:43:01 -0700, Chrissy
<Chrissy(a)discussions.microsoft.com> 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]


From: Chrissy on 12 May 2010 17:46
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
> <Chrissy(a)discussions.microsoft.com> 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]
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Show only this year
Next: Group By or MAX Question