locked
Date Compare Function RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello All,

    Apologies if this is an obvious solution, I am relatively new to Access, this is my first database not just managed in Excel. I’ve completed some uDemy courses which covers most of what I need, however there is one feature I would like to implement which I have not been able to.

    The situation is that I have an Access 365 database, which stores requests from staff for shifts. As part of entering this data I want the form to reject applications where the cut off date for a given roster has gone by. I have created a table which has three columns; “RosterStart”, “RosterFinish”, and “CutOffDate”. In Excel I had used a combination of the OFFSET and the MATCH function to find the cut-off date from an inputted date against a lookup table, how could this be achieved in Access?

    In short, I am looking for the database to look for which roster the entered date falls under, then look at the CutOffDate for that roster, compare it against system date, and generate an error, as needed.

    Your assistance would be greatly appreciated.

    Sunday, January 5, 2020 1:28 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    In the BeforeUpdate event procedure of the control in which the requested date is entered put code along these lines:

    Const MESSAGE_TEXT = Cut off date for the roster has been passed."
    Dim dtmCutOfDate As Date
    Dim strCriteria As String
    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then
        strCriteria = "#" & Format(ctrl,"yyyy-mm-dd") & "# Between RosterStart And RosterFinish"
        dtmCutOffDate = DLookup("CutOffDate", "Rosters", strCriteria)
        If dtmCutOffDate < VBA.Date Then
            MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
            Cancel = True
        End If
    End If

    Ken Sheridan, Stafford, England

    • Marked as answer by LucasRG Friday, January 10, 2020 7:49 PM
    Sunday, January 5, 2020 1:38 PM