CODEWITHSUNDEEP
ms-accessvba
AndrewL73
AndrewL73

Reputation: 23

Compare date from form with date in table

Building a booking system on Access 2016 I want to do some validation for a new booking to ensure the facility is available. The problem I seem to be is comparing dates coming from the form with the dates in the table during the SQL.

Here is some code to try and debug this:

    Set rst = CurrentDb.OpenRecordset("SELECT [FacBookID],[FacilityID],[EventDate] from FacilitiesBooking where [FacilityID] = " & Me.Facilities.Column(0, facilitySelect))
    Debug.Print Me.Facilities.Column(0, facilitySelect) & " | " & Me.EventDate.Value & " | " & rst("FacilityID") & " | " & rst("EventDate") & " | " & rst("FacBookID")
    rst.Close
    Set rst = CurrentDb.OpenRecordset("SELECT [FacBookID] from FacilitiesBooking where ([FacilityID] = " & Me.Facilities.Column(0, facilitySelect) & " AND [EventDate] = " & Me.EventDate.Value & ")")
    If Not rst.EOF Then
        Debug.Print rst("FacBookID")
    Else
        Debug.Print "Did not find"
    End If
    rst.Close

When I run this with only one entry in the table and form data to match this entry I get:

3 | 04/07/2016 | 3 | 04/07/2016 | 2

Did not find

This implies to me that I should get a record back from the second of the SQL statements but for some reason I do not.

Apologies if this is something dumb but I have done a number of searches and tried different formatting methods for the date from the form.

Upvotes: 2

Views: 462

Answers (1)

Gustav
Gustav

Reputation: 55806

You must use a formatted string expression for the date value:

Set rst = CurrentDb.OpenRecordset("select [FacBookID] from FacilitiesBooking where ([FacilityID] = " & Me!Facilities.Column(0, facilitySelect) & " AND [EventDate] = #" & Format(Me!EventDate.Value, "yyyy\/mm\/dd") & "#)")

Upvotes: 1

Related Questions