MS Access 365, data validation

Question

Good afternoon,

Again, I am a complete novice at this!!

I have another question regarding the holiday let database I am trying to build.

I have one table that stores the booking information and a form for entering data into it. The table fields are:

What I'm trying to achieve is: when a new booking is entered via the booking form the arrival date required is checked against existing bookings. If the date falls on the dates of another booking for the same holiday cottage an error message is given and the booking record can not be saved.

For example: If holiday cottage 1 is booked with an arrival date of 01/01/2022 for a period of 3 nights another booking for holiday cottage 1 can not be entered for 01/01/2022, 02/01/2022 and 03/01/2022. The next available date that could be entered would be 04/01/2022. I do not need it to suggest other properties if they are available.

With that in mind I have been using the 3 known fields: Property(Bookingtble_FHLtblUI). Arrival date and number of nights to validate new records.

My first attempt was to use the validation rule on the field properties for Arrival date to ensure that dates entered could not be in the past. This was: >=Date(). This worked.

I then tried to expand this so that dates added were checked against existing bookings (or so I thought) by using >=Date() And [Arrival_date]>=[Arrival_date]+[Number_of_nights]. That didn't work, got an "Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint." error message.

So I tried [Arrival_date]>=[Arrival_date]+[Number_of_nights] in the table properties validation rule. That give me: "One or more values are prohibited by the validation rule '[Arrival_date]>=[Arrival_date]+[Number_of_nights]' set for 'Booking_tbl'. Enter a value that the expression for this field can accept" error.

This happened for dates that had no conflict with other bookings.

Am I in the right ball park? or should I be tackling this a completely different way? I did think about having a "leaving date" instead of "number of nights" and setting both the arrival and leaving dates to indexed no duplicates - this became non workable very quickly because we can have guests leaving and arriving on the same day.

I know that this is probably asked a thousand times, but how do I do it?

All and any help, criticism and guidance would be received with great gratitude.

Answer 1

The code I posted in my original reply is that for the WithinDateRange function.  You should paste it into any standard module in the database, and be sure to save the module under a name which differs from that of the function or any other object in the database.  In my case the module is named basDateStuff.

In my case the function is called in the RowSource query of a combo box to restrict the combo box's list to those rooms where the booked date ranges do not intersect with the date range defined by the start and end dates entered into the booking form, i.e. those rooms which are available for the whole of the desired data range. In the query the Nz() function is used to return a date far into the future if no end date is entered.  Consequently the booking is open ended , and will only be allowed for rooms which have no bookings on or after the desired start date.

If you want to use it to validate a range entered into a form then the form's BeforeUpdate event procedure would be an appropriate place.  To do this you should first create a query which calls the function, in the same way that the query I posted in my original reply does, referencing the controls in your form as parameters.  To detect where there is an intersection of the ranges, and the booking is therefore invalid, you'd call the Dlookup function to determine whether a row is returned by the query where there would be an intersection of the ranges.  The query would consequently be like this:

PARAMETERS Forms!YourFormName!Booking_tbl_FHtblUI LONG,

Forms!YourFormName!ArrivalDate DATETIME;

SELECT *

FROM Booking_tbl

WHERE Booking_tbl_FHtblUI = Forms!YourFormName!Booking_tbl_FHtblUI

     AND WITHINDATERANGE(Forms!YourFormName!ArrivalDate,

     Forms!YourFormName!ArrivalDate+Forms!YourForm!Number_Of_Nights,

     Arrival_Date, Arrival_Date+Number_Of_Nights);

The code to validate the dates entered in the form would be like this:

    Const MESSAGE_TEXT = "One or more of the dates selected have already been booked."

    If Not IsNull(DLookup("Booking_tbl_UI","YourQueryName")) Then

         MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

         Cancel = True

    End If

You'll appreciate that I'm unable to test the above without access to your file, so cannot rule out the possibility that some debugging might be required.

Answer 2

Ken,

You're a star!

Here's what I did:

Module Doublebookingcheck:

Public Function WithinDateRange(dtmParamRangeStart As Date, _

                   dtmParamRangeEnd As Date, \_ 

                   dtmDataRangeStart As Date, \_ 

                   dtmDataRangeEnd As Date) As Boolean 

' Accepts: date at which parameter range starts

' date at which parameter range ends

' date at which data range starts

' date at which data range ends

' Returns: True if data range intersects with parameter range

' False if whole of data range outside parameter range

WithinDateRange = _

   dtmDataRangeStart <= dtmParamRangeEnd And \_ 

   dtmDataRangeEnd >= dtmParamRangeStart 

End Function

Just as yours.

Then Query Doublebook_qry:

PARAMETERS Forms!Booking_frm!Bookingtbl_FHLtblUI Long, Forms!Booking_frm!Arrival_date DateTime;

SELECT *

FROM Booking_tbl

WHERE Bookingtbl_FHLtblUI = Forms!Booking_frm!Bookingtbl_FHLtblUI

 AND WITHINDATERANGE(Forms!Booking\_frm!Arrival\_date, 

 Forms!booking\_frm!Arrival\_date+Forms!Booking\_frm!Number\_of\_nights, 

 Arrival\_date, Arrival\_date+Number\_of\_nights); 

And finally "before update event":

Private Sub Arrival_date_BeforeUpdate(Cancel As Integer)

Const MESSAGE_TEXT = "One or more of the dates selected have already been booked."

If Not IsNull(DLookup("Bookingtbl\_UI", "Doublebook\_qry")) Then 

     MsgBox MESSAGE\_TEXT, vbExclamation, "Invalid Operation" 

     Cancel = True 

End If 

End Sub

Better than Christmas!!

Thanks a lot

PS, Have you got a recommendation for a book on VBA, Access etc

Answer 3

Good Morning Ken,

I have had a look at your database and I think that I have established that the function you gave above is a macro.....

I now realise that I know one thing, and that is: I know nothing about databases! Are there any books out there that are comprehensive that you recommend?

I'm not sure how I translate your function so it works with my database.

Does this go into a control on my Booking Form instead of the actual "Booking table"?

How do I incorporate the number of nights stayed and account for the chosen property?

Would I be better off having a "Leave Date" instead of "Number of nights required"

Taking into account what Daniel said above I think I now understand that the validation rule for the table was giving me the error because it was only checking the date calculation against required new entry, therefore it was bound to fail because the new entry will always fall within it's own date range.

Thanks for your help

Answer 4

The WithinDateRange funcrion is not a macro, it's a VBA function in the basDateStuff module in the database, and acts just like a built in function in that values are passed into the function as arguments, and, on the basis of those values the function returns a value.  In this the return value is a Boolean TRUE or FALSE, i.e. it returns a TRUE if the two date ranges passed into the function intersect, and a FALSE otherwise.  You can see this if we call the function from another little function with some literal values:

Public Function DoubleBooked() As Boolean

    Dim StartDateParam As Date

    Dim EndDateParam As Date

    Dim StartDateData As Date

    Dim EndDateData As Date

    StartDateParam = #2/15/2022#

    EndDateParam = #2/20/2022#

    StartDateData = #2/18/2022#

    EndDateData = #2/25/2022#

    DoubleBooked = WithinDateRange(StartDateParam, EndDateParam, StartDateData, EndDateData)

End Function

If we then call this function in the immediate window:

? DoubleBooked()

True

As you can see it returns TRUE because the two date ranges intersect, both containing the dates 18 Feb to 20 Feb.

If we then amend the start of the data range:

StartDateData = #2/21/2022#

and call the function again:

? DoubleBooked()

False

This time it returns FALSE because there is no intersection of the two ranges, the data range now starting on the day after the parameter range finishes.

The logic is quite simple; the start of the first range must be on or before the end of the second range, and the end of first range must be on or after the start of the second range for the ranges to intersect by one day or more.  You can if you wish simply express the logic in an expression rather than calling the WithinDateRange function.  Using a function simply means you don't have to repeat the logical expression each time, just pass the values as arguments into the function.

As for having a start and end date, or a start date and the number of days booked, if you do the latter you would just add the number of days to the start dates as the second and fourth arguments:

WithinDateRange(StartDateParam, StartDateParam+NumberOfDaysParam, StartDateData, StartDateData+NumberOfDaysData)

Where you call the function depends on where you need to check for double booking, but in a form the Form's BeforeUpdate event procedure would be appropriate as this has a Cancel argument whose return value can be set to TRUE if double booking is detected.  In my demo I pre-empt the selection of a booked room by restricting a combo box's list to those rooms not occupied on any day within the selected range.  This is done by calling the function in the combo box's RowSource query, referencing the two text boxes in the form as parameters:

PARAMETERS Forms!frmOccupants!sfcReservations!EntryDate DATETIME,

Forms!frmOccupants!sfcReservations!DepartureDate DATETIME;

SELECT RoomNumber

FROM Rooms

WHERE NOT EXISTS

    (SELECT *

     FROM RoomOccupations

     WHERE Rooms.RoomNumber = RoomOccupations.RoomNumber

     AND WITHINDATERANGE(Nz(Forms!frmOccupants!sfcReservations!EntryDate,#1900-01-01#),

     Nz(Forms!frmOccupants!sfcReservations!DepartureDate,#2100-01-01#),

     EntryDate,DepartureDate));

Answer 5

Ken,

Thanks for trying to help me.

However, I am very confused. Do I input your first code in your first reply into the "Before Update" event for my Arrival date field or do I put it somewhere else? If it goes somewhere else, how do I use it to check that the Arrival date I am inputting is checked against existing data? What starts it?

Honestly, I have not got a clue when it comes to things like this.

If you can show me that would be great. But if I've taken up too much of your time, I understand and thank you for the help you've already given me.

Thanks