Search function based on range of dates

[navi95]

Hi guys,

I have made a booking system in access which works brilliantly. There is a feature I would like to include and I thought I would ask you guys, as last time you was able to help as well.
Essentially I want a query which will ask me to input the required dates of the booking and I want the query then to show me which apartment is available.

So I have a booking table with the Customer details, which apartment is booked and the arrival and departure etc

Is this possible? If so, do you require any more information first? Or am I overlooking a very simple solution?

Regards,

Nav

 

[pbaldy]

A query like this will find what is booked:

http://www.baldyweb.com/OverLap.htm

You can the use the unmatched query wizard to compare that to the table of apartments to get those available.

 

[navi95]

Thanks for the reply, that page seems like exactly what I need but I dont quite understand how I meant to translate that into a query. As you can im still quite a noob when it comes to access.

 

[navi95]

help..please? lol I would also need to input two sets of dates, the arrival and departure date.

 

[arnelgp]

create a Parameter query, on query design click Parameters on the ribbon.

 

[jdraw]

You may find this sample helpful.

 

[navi95]

I'll be home soon, ill have a play around with the parameter query.

I had a look at that SQL code, the code itself seems simple enough but Ive never dabbled with SQL, i don't even know where I would input that code :/

 

[navi95]

Ive found other people mentioning that webpage regarding a room availability query, and how its explained on the website it does seem perfect for what I need.

But I need help with the SQL code:

SELECT Driver, OffStartDate, OffEndDate, Reason
FROM tblSickVac
WHERE OffStartDate <= Forms!FormName.txtEndDate And OffEndDate >= Forms!FormName.txtStartDate

I just need this broken down so that I know where to input my field names. I would like to understand how the code works, rather than someone filling in the blanks

And another thing, I also need this to show Rooms available from the same day that the previous client left.

For example:

If room A is booked: 01.01.2016 - 20.01.2016
room B is booked: 01.01.2016 - 30.01.2016
room C is booked: 10.01.2016 - 19.01.2016

If I type in: 20.01.2016 - 31.01.2016

Then I want Room A and C to be shown in the results.

 

[jdraw]

Part of the issue is that your enddate for Room A is the same as the StartDate of your

If I type in: 20.01.2016 - 31.01.2016

I found this, based on the referenced link, does what you are asking

 PARAMETERS ResStart DateTime, ResEnd DateTime;
SELECT navi95.room 
FROM navi95
WHERE (navi95.room ) NOT IN 
( SELECT DISTINCT Room  FROM 
 (
   SELECT   x.Room , x.startDT, x.Enddt
   FROM  navi95 as X
   WHERE
  
     X.StartDt  Between  ResStart AND ResEnd
   Or 
     X.EndDt Between[COLOR="Red"][B] ResStart +1[/B][/COLOR]  And ResEnd
   Or  
    X.startDt<= ResStart And X.Enddt>=ResEnd)
  );

TheResStart +1 was needed to resolve the Same date issue mentioned above. There may be other techniques.

If you had a CheckoutTime for such a Date, then you could use the query shown in post #4 in the referenced link.


Good luck.

 

[navi95]

Right, time to figure this out! Had to put it on the backburner....

Ive got this SQL so far now:

PARAMETERS ResStart DateTime, ResEnd DateTime;
SELECT tbl_Apartments.Apartment
FROM tbl_Apartments
WHERE ((([tbl_Apartments].[Apartment]) Not In (SELECT DISTINCT Apartment FROM
(
SELECT tbl_Apartments.Apartment , [tbl_Bookings.Date Of Arrival], [tbl_Bookings.Date of Departure]
FROM tbl_Bookings, tbl_Apartments
WHERE

[tbl_Bookings.Date Of Arrival] Between ResStart AND ResEnd
Or
[tbl_Bookings.Date of Departure] Between ResStart +1 And ResEnd
Or
[tbl_Bookings.Date Of Arrival]<= ResStart And [tbl_Bookings.Date of Departure]>=ResEnd)
)));

Now When I run the query it asks for the Input of "ResStart" and "ResEnd" and if I enter dates I get no results and when I Enter and Enter through the two Input Prompts I just get a list of all my apartments, regardless of them being booked or not.

 

[pbaldy]

Can you attach your db here with some sample data?