Date range search

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

Hi,


I have a table where I have short term accomodation (150
locations). I have an incoming date and an outgoing date.
To save me time how can I set my query up so that:

I can punch in an incoming date and an outgoing date and
have it return any accom sites which are occupied during
any part or all of the entered date range. At a loss.

Beth
 
Beth, there could be many aspects to this question, but this article may
prove useful:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

If all locations take bookings for whole days only, it may be more efficient
to create related entries for each date in the range rather than specifying
a StartDate EndDate for the booking.
 
Hi! I not that clear as to what exactly you require

But if you want to have a query that brings back results under
certain date range you could try entering this in the criteria of you
date field.

Between[Enter start Date]And[Enter End Date]

You may need to change one of the date fields. I presume you have tw
date fields. 1 For beginning date and 1-for leaving date.

If so try entering Just [enter start date] in the criteria under you
start date and [enter leaving date] under your leaving date.

Hopefully this may be of help, if not can you give me more details
 
I am not exactly sure as to what it is you actually need.

Try entering [IncomingDate] under that field in the
criteria
and [Outgoing Date] under that field in the criteria in
the query. Once you open your query you will be asked for
these values, and once entered all corresponding dates
will be returned

I hope I was of some help if not can you post more
information about what you want.
 
From your description, I am guessing that you have a record something like

StudentID, RoomNumber, DateIN, and DateOut.

You are trying to get roomnumbers that are occupied between two dates
(StartPeriod, EndPeriod)

That means that the DateIN should be BEFORE the EndPeriod and DateOut Should be
AFTER StartPeriod. This will get every room that is occupied AT SOME TIME
during the StartPeriod to EndPeriod.

WHERE DateIN <=[End Period] AND DateOut >= [Start Period]
 
Thank you I do see the logic needed to achieve this now....
my difficulty however is that say I need to make a booking
for T1-T2. Bookings may have been made for rooms which:

bracket T1-T2 entirely
bracket T2 only
bracket T1 only
Start on T2
Finish on T1

In any of these cases I cannot book into this room. It is
like I am looking for a room which satisfies any of the
above so I know I cannot book in to those. Am I just
hoping for too much on basic knowledge?

regards

Beth




-----Original Message-----
From your description, I am guessing that you have a record something like

StudentID, RoomNumber, DateIN, and DateOut.

You are trying to get roomnumbers that are occupied between two dates
(StartPeriod, EndPeriod)

That means that the DateIN should be BEFORE the EndPeriod and DateOut Should be
AFTER StartPeriod. This will get every room that is occupied AT SOME TIME
during the StartPeriod to EndPeriod.

WHERE DateIN <=[End Period] AND DateOut >= [Start Period]


Hi,

I have a table where I have short term accomodation (150
locations). I have an incoming date and an outgoing date.
To save me time how can I set my query up so that:

I can punch in an incoming date and an outgoing date and
have it return any accom sites which are occupied during
any part or all of the entered date range. At a loss.

Beth
Click to expand...
.
Click to expand...
 
I think that the query criteria I gave you will identify records in the category
of being occupied some time during the specified frame. Are you now saying that
you want the opposite - rooms that will not be occupied at all during the time frame?
Thank you I do see the logic needed to achieve this now....
my difficulty however is that say I need to make a booking
for T1-T2. Bookings may have been made for rooms which:

bracket T1-T2 entirely
bracket T2 only
bracket T1 only
Start on T2
Finish on T1

In any of these cases I cannot book into this room. It is
like I am looking for a room which satisfies any of the
above so I know I cannot book in to those. Am I just
hoping for too much on basic knowledge?

regards

Beth
-----Original Message-----
From your description, I am guessing that you have a record something like

StudentID, RoomNumber, DateIN, and DateOut.

You are trying to get roomnumbers that are occupied between two dates
(StartPeriod, EndPeriod)

That means that the DateIN should be BEFORE the EndPeriod and DateOut Should be
AFTER StartPeriod. This will get every room that is occupied AT SOME TIME
during the StartPeriod to EndPeriod.

WHERE DateIN <=[End Period] AND DateOut >= [Start Period]


Hi,

I have a table where I have short term accomodation (150
locations). I have an incoming date and an outgoing date.
To save me time how can I set my query up so that:

I can punch in an incoming date and an outgoing date and
have it return any accom sites which are occupied during
any part or all of the entered date range. At a loss.

Beth
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top