Query or Message Box that identifies when data is being entered between existing date ranges

[rondon]

I havecreated a table - Bookings_tbl with Key being Booking ID. It has two fields Arrival and Departure based on Date/Time. When a new booking is created bothfields must be entered. Eg


BookingID - 1
Arrival –12/09/2015
Departure– 16/09/2015
I would like to create a query or better still a message box that warns the user ifthey try to create a new booking (Arrival) that falls on or between an existing Arrival and Departure date
Any help ideas would be appreciated thanks Ron

[JeroenMioch]

 
Dim strSQL As String
  strSQL = "SELECT DISTINCT BookingID FROM YourTable " _
    & "WHERE BookingID = '" & Me.YourBookingField & "' AND Date() between [ArrivalDate]  and [DepartureDate]"
  With CurrentDb.OpenRecordset(strSQL)
    If .RecordCount > 0 Then
 Msgbox "Not available"
    Else
 Msgbox "available"
    End If
End With
    End If
    
    Set rst = Nothing

YourTable is the name of the table that holds the data.
For me this method works every time.

Let me know if you have more questions.

Greetings : Jeroen

[rondon]

Hello Jeroen
I entered thecode in against the Arrival field in the Bookings_frm but when I run the code I get an error. I have tried researching this as I would like to try and get my own head around it but I have not made any headway.Below is the entered code
Private Sub Form_AfterUpdate()
Dim strSQL As String
strSQL ="SELECT DISTINCT BookingID FROM Bookings_tbl " _
&"WHERE BookingID = '" & Me.Arrival & "' AND Date() between[ArrivalDate] and [DepartureDate]"
WithCurrentDb.OpenRecordset(strSQL)
If.RecordCount > 0 Then
MsgBox"Not available"
Else
MsgBox"available"
End If
End With
End If
Set rst = Nothing
End Sub

I get the following error message End If without Block IF. I have tried to enter additional code /comment out existing but to no prevail any help would be greatly recieved

thanks Ron

[JeroenMioch]

I think you should put the code in the Afterupdate event of the last textbox you fill in. That should be depatureDate i guess.
The last "If" in my statement was one too many. Sorry for that mate
Also you have to have a control (Textbox) on your form thats called ctrlBookingID.
In this control you need to have the BookingID present, otherwise it wont work.
This number is so Access can identify wich record it needs to check.

The ID number is usually an AutoID (Access grants every record a number), Personell number, Social security number, licence plate number..etc So basicly every number that is unique to identify a customer, personel, vehicle, roomnumber and so on.

If your table design looks like this :

BookingID, ArrivalDate, DepartureDate

Then these fields need to be present on your form :

ctrlBookingID, ctrlArrivalDate, ctrlDepartureDate

I put ctrl in front of every textbox's name so there is no confusion for Access (or myself for that matter) if im referring to fields in the table or controls on the form.

Private Sub Form_AfterUpdate()
Dim strSQL As String

strSQL ="SELECT DISTINCT BookingID FROM Bookings_tbl " _
&"WHERE BookingID = '" & Me.ctrlBookingID & "' AND Date() between[ArrivalDate] and [DepartureDate]"

WithCurrentDb.OpenRecordset(strSQL)
If.RecordCount > 0 Then
MsgBox"Not available"
Else
MsgBox"available"
End If
End With

Set rst = Nothing
End Sub

[rondon]

Hi Jeroen
I have tried to make the code you provided work, I am getting errors but wanted to have a go myself, however again I have come to a standstill.
Find below what I have entered I keep getting the following message - Complie Error Invalid or Unqualified Reference and the line of code If .RecordCount > 0 Then. turns blue. Again any help would be greatly received

Ron


Private Sub Departure_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT Booking ID FROM Bookings_tbl " _
& "WHERE Booking ID = '" & Me.ctrlBookingID & "' AND Date() between[ctrlArrival] and [ctrlDeparture]"
WithCurrentDb.OpenRecordset (strSQL)
If .RecordCount > 0 Then
MsgBox "Not available"
Else
MsgBox "available"
End If
End With
Set rst = Nothing
End Sub

[JeroenMioch]

Hi Rondon,

I see a problem that unfortunately i didnt spot before.
<edit> the space wasnt in the original code, so why was it in your last post ?

Your field Booking ID has a space in it.
Access sees this as two seperate words and this causes an error.

You can refer to the field in brackets [Booking ID] but the best thing is to never allow any space in a field or controls name.
So your best off changing Booking ID to BookingID and adjust my code accoringly :

Private Sub Departure_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT BookingID FROM Bookings_tbl " _
& "WHERE BookingID = '" & Me.ctrlBookingID & "' AND Date() between [ctrlArrival] and [ctrlDeparture]"
WithCurrentDb.OpenRecordset (strSQL)
If .RecordCount > 0 Then
MsgBox "Not available"
Else
MsgBox "available"
End If
End With
Set rst = Nothing
End Sub

i also spotted a missing space here : between[ctrlArrival]
Ive corrected that in the code above.

[JeroenMioch]

Ron im gonna make a sample db for you.

What are the bookings , are these rooms in a hotel, or rental cars ?

[ItsMe]

The example in post #2 is using a function where there should be the name of a column.
Date()

An expression for a WHERE clause using dates and the BETWEEN operator would look more like
Dim strWhere As String
strWhere = "WHERE [MyDateField] BETWEEN #" & Me.TextBoxName1 & "# AND #" & Me.TextBoxName2 & "#"

@rondon
I think the first thing you should do is create a query the retrieves records for active bookings. Post the SQL here and also the names of any date fields you want to check.

[rondon]

Jeroen
It is for bookingthe Marae (traditional Māori meeting place). We can only have one group at the Marae at any time. The group may arrive and stay at the Maraefor a day or for a week depending on what event is happening. Everyone sleeps in the main hall so we identify the booking as a single event. Booking ID (yes I understand what you said about this and realise my mistake with the space in this field name). I have changed this in the form and subforms

Fields Require:
Booking ID –Auto number
Group - Text
Purpose - Text
Arrival - Date/Time
Departure - Date/Time
ctrlBooking ID
ctrlArrival ID
ctrlDeparture ID
We also havesome sub forms-Contacts etc. We will build on this by adding other required fields once I sort outhow not to double book the Marae

[JeroenMioch]

Im happy to try it for you, but i must say that there are better products out there that fits your needs.
Take a look at Outlook's agenda for example. You can also keep track of your contacts there.
Its in my nature to try to help people and ive been thinking for a way to make something in Access for you,
But reinventing the wheel might not be the way to go for you.

You need a graphical way to look at your planning board. To make that in Access, requires some serious coding and know-how.

[rondon]

Thanks, Iexplored what you suggested but we intend to capture lots of other info likethe lifecycle of the booking from being penciled in to confirmed or cancelled etc. aswell the form emails reports to our tunu kai (catering) staff, to ourKaumatua (elders) who welcome the visitors onto the Marae etc. To date thedatabase meets our need very well except for having to search if abooking exist to prevent the double bookings.

[JeroenMioch]

Right, i have a tested way of doing it -with a little help of ItsMe's code.

Private Sub ctrlDeparture_AfterUpdate()

Dim ctrlArrival As Date
Dim ctrlDeparture As Date
Dim strSQL As String


strSQL = "SELECT Arrival, Departure FROM Bookings_tbl " _
& "WHERE [Arrival] BETWEEN #" & Me.ctrlArrival & "# AND #" & Me.ctrlDeparture & "#" _
& "OR [Departure] BETWEEN #" & Me.ctrlArrival & "# AND #" & Me.ctrlDeparture & "#"
With CurrentDb.OpenRecordset(strSQL)
If .RecordCount > 0 Then
MsgBox "Not available"
Else
MsgBox "available"
End If
End With


Set rst = Nothing
End Sub

But allthough this is a beginning, its not the way to do it.
You need a way to filter your records, and some unbound textboxes to check the availability.
But first test my piece of code and let me know how that works out.

[rondon]

Thanks, this work well, it is a good start

[ItsMe]

@rondon
I suggest you define some business rules. For instance, will every Booking that has ever been created always be considered when checking for existing bookings? What defines a booking as 'Active'? If a booking starts on the 1st and ends on the 3rd, would a booking request for the 2nd through the 5th be OK? Are you creating a booking for only one entity? Let's say we are creating bookings for a Car Rental. Is there only one car available for rent, ever?

[rondon]

Thank you

1. All bookings will have a Arrival date and a Departure date these fields are set to Required _Yes
2. Only bookings which have an Arrival date which is earlier or the same as a recorded Departure date in the table will need to be checked
3. A booking is active unless the Closed field is checked. This field is Data Type = Yes/No set to Format = True/False and presents on the form as a check box
4. If a booking starts on the 1st and ends on the 3rd, would a booking request for the 2nd through the 5th be OK - A booking request for the 2nd to the 5th would need to checked by the user as the Arrival date 2^nd is earlier than the other bookings Departure date of the 3^rd (After checking the other booking the user may proceed to book the request) therefore the Booking ID if displayed in the message box would be of value
5. Yes there is only one entity = the Marae. Although different parts of the Marae may be used at the same time by different groups. Each booking is deemed to be for the whole Marae see rule 4

Thanks