A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query and date selection



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 08:43 PM posted to microsoft.public.access.queries
Chrissy
external usenet poster
 
Posts: 93
Default 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
Ads
  #2  
Old May 12th, 2010, 08:55 PM posted to microsoft.public.access.queries
ghetto_banjo
external usenet poster
 
Posts: 325
Default 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  
Old May 12th, 2010, 09:01 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old May 12th, 2010, 09:26 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 12th, 2010, 10:46 PM posted to microsoft.public.access.queries
Chrissy
external usenet poster
 
Posts: 93
Default 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  
Old May 13th, 2010, 12:09 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 13th, 2010, 12:44 AM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old May 13th, 2010, 01:04 AM posted to microsoft.public.access.queries
Chrissy
external usenet poster
 
Posts: 93
Default 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  
Old May 13th, 2010, 03:07 PM posted to microsoft.public.access.queries
Chrissy
external usenet poster
 
Posts: 93
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.