Results 1 to 6 of 6
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Validate Start and End Dates are between a range of dates

    This is the last thing I need to finish this database. It was actually due yesterday, OCT 1.

    I have 4 fields: I need to check the Vacancy Start date is before the Vacancy End date AND they fall between TOStart and TOEnd (TaskOrder)
    VACStart - Entered by user via textbox on form - This field is required before I can write the record.


    VACEnd - Entered by user via textbox on form - This field is not always required because we don't always have an End date when the record is created.
    TOStart - Provided in textbox based on the selected TaskOrder on the form.
    TOEnd - Provided in textbox based on the selected TaskOrder on the form.

    When a user selects the TaskOrder on the form my code displays the correct TaskOrder Start and End date so the user know what the period of performance is.
    Code:
    Private Sub cboTaskOrder_Click()
            Me.txtTOStartDate = Me.cboTaskOrder.Column(1)
            Me.txtTOEndDate = Me.cboTaskOrder.Column(2)
            Me.txtMTFDTF = Me.cboTaskOrder.Column(3)
            Me.txtCOR = Me.cboTaskOrder.Column(4)
            Me.cboCLIN.Requery
    End Sub
    PROBLEM I need to check the VACStart and/ possibly the VACEnd date are between the TOStart and TOEnd. Here is code but I am not sure how to check the TaskOrder range. Once this is working I can modify it for the VACStart when that is the only field provided.

    If the VACEnd date is not provided on the Data Entry form it can be updated at a later time on the Edit form which would use this same logic.

    Code:
    Private Sub txtVACEnd_AfterUpdate()
         Call IsValidDate
    End Sub
    
    Private Function IsValidDate(VACStart As Date, VACEnd As Date) As Boolean
     Dim strMsg As String
     
     If IsDate(VACStart) = True And IsDate(VACEnd) = True
           If VACStart <= VACEnd Then
            strMsg = "Start Vacancy Start Date must be EQUAL TO or LESS THAN Vacancy End Date."
        End If
     Else
        strMsg = "Vacancy Start Date is required."
     End If
      
     If Len(strMsg) = 0 Then
         IsValidDate = True
     Else
        MsgBox strMsg, vbOKOnly, "Date Entry Error" 'Windows Message Box
        IsValidDate = False
     End If
    End Function
    I would appreciate any help you can provide.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    Private Function IsValidForm() As Boolean
    Dim vMsg
    Select Case True
       CASE not iSDate(vacStart)
          vMsg = "vacStart is not a date"
     
       CASE iSnULL(vacStart)
          vMsg = "vacStart is missing"
     
       CASE iSnULL(vacEnd)
          vMsg = "vacEnd is missing"
    
    
       CASE ISDate(vacStart) and ISDate(vacEnd)
           if VacStart > VacEnd then  vMsg = "vacStart must be before the VacEnd date"
    
    
    End Select
    If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
    IsValidForm = vMsg = ""
    End Function

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Thanks.

    How do I check the VACStart and VACEnd date to make sure they are within the TOStart and TOEnd dates in those fields?

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you want to just see in one date is between two others, you would just do so like this:
    Code:
    If (MyDate >= StartDate) and (MyDate <=EndDate) Then ...

  5. #5
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    ranman256 and JoeM
    I have update my code but when I tested it , it didn't seem like it was running. Where do I kick off the IsValidForm() from? I am use to using the Private Sub from and Event Procedure.

    Code:
    Private Function IsValidForm() As Boolean
    Dim vMsg
    Select Case True
       Case Not IsDate(VACStart)
          vMsg = "Vacancy Start is not a date"
     
       Case IsNull(VACStart)
          vMsg = "Vacancy Start is missing"
     
       Case IsDate(VACStart <= TOStart) And (VACStart >= TOEnd)
          vMsg = "Vacancy Start Date must be between Task Order Start and End Date."
       Case IsDate(VACStart) And IsDate(VACEnd)
           If VACStart > VACEnd Then vMsg = "vacStart must be before the VacEnd date"
       
       Case IsDate(VACEnd >= TOStart) And (VACEnd <= TOEnd)
          vMsg = "Vacancy End Date must be between Task Order Start and End Date."
    End Select
    If vMsg <> "" Then MsgBox vMsg, vbCritical, "Vacancy Start Date is a Required Field"
    IsValidForm = vMsg = ""
    End Function

  6. #6
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I realized after some testing that I need this routine embedded in my AddVAC code. I added the Select CASE section but it is not working. The rest of this code works correctly as I have been using it for months. It is running the first 2 routines of IF statements but it does nothing when it gets to the Select CASE.

    Code:
    Private Sub cmdAddVAC_Click()
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Variant
        Dim vacancyValue As String
        Dim LResponse As Integer
        Dim vMsg
        Dim TOStart As Date
        Dim TOEnd As Date
        
        On Error GoTo ErrorHandler
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblPerfIssues")
        If Len(Me.cboAnalystSpecialist & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNum & vbNullString) = 0 Then
            MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
        Exit Sub
        End If
              
        If Len(Me.cboTaskOrder & vbNullString) = 0 Or Len(Me.cboCLIN & vbNullString) = 0 Or Len(Me.lstVACReason & vbNullString) = 0 Or Len(Me.txtIndHours & vbNullString) = 0 And Len(Me.txtCovHours & vbNullString) = 0 Or Len(Me.txtVACStart & vbNullString) = 0 Then
            MsgBox "Please select a Task Order, CLIN, Vacancy Reason, Individual or Coverage Hours and enter a Vacancy Start Date."
            Exit Sub
        End If
        
       Select Case True
        Case Not IsDate(VACStart)
          vMsg = "Vacancy Start is not a date"
     
        Case IsDate(VACStart <= TOStart) And (VACStart >= TOEnd)
          vMsg = "Vacancy Start Date must be between Task Order Start and End Date."
        Case IsDate(VACStart) And IsDate(VACEnd)
           If VACStart > VACEnd Then vMsg = "vacStart must be before the VacEnd date"
       
        Case IsDate(VACEnd >= TOStart) And (VACEnd <= TOEnd)
          vMsg = "Vacancy End Date must be between Task Order Start and End Date."
        End Select
           
                 With rs  '<<--!!!!
                    .AddNew
                    !AnalystSpecialist = Me.cboAnalystSpecialist
                    !ReportDate = Me.txtReportDate
                    !ContractNum = Me.cboContractNum.Column(0)
                    !Contractor = Me.txtContractor
                    !NameofMATO = Me.txtNameofMATO
                    !TaskOrder = Me.cboTaskOrder
                    !CLIN = Me.cboCLIN.Column(2)
                    !COR = Me.txtCOR
                    !MTFDTF = Me.txtMTFDTF
                    !LaborBand = Me.txtLaborBand
                    !LaborCat = Me.txtLaborCat
                    !SiteofService = Me.txtSiteofService
                    !ClinicalArea = Me.txtClinicalArea
                    !IndCov = Me.txtIndCov
                    !MissedHours = Me.txtIndHours
                    !MissedShifts = Me.txtCovHours
                    !TOStartDate = Me.txtTOStartDate
                    !TOEndDate = Me.txtTOEndDate
                    !VACStart = Me.txtVACStart
                    !VACEnd = Me.txtVACEnd
                    !VACReason = Me.lstVACReason.Column(0)
                    !IssueKey = Me.lstVACReason.Column(1)
                    !VACComments = Me.txtVACComments
                    .Update
                End With '<<--!!!!
    LResponse = MsgBox("Change to a new Task Order?", vbYesNo, "Continue")
    If LResponse = vbYes Then
                    Me.cboTaskOrder.Value = Null
                    Me.cboCLIN.Value = Null
                    Me.txtCOR.Value = Null
                    Me.txtMTFDTF.Value = Null
                    Me.txtLaborBand.Value = Null
                    Me.txtLaborCat.Value = Null
                    Me.txtSiteofService.Value = Null
                    Me.txtClinicalArea.Value = Null
                    Me.txtIndCov.Value = Null
                    Me.txtIndHours.Value = Null
                    Me.txtCovHours.Value = Null
                    Me.txtVACStart.Value = Null
                    Me.txtVACEnd.Value = Null
                    Me.lstVACReason.Value = Null
                    Me.txtVACComments.Value = Null
                    Me.txtIssueKey = Null
                    Me.txtIndHours.Enabled = True
                    Me.txtCovHours.Enabled = True
    Else
                    Me.txtIndHours.Value = Null
                    Me.txtCovHours.Value = Null
                    Me.txtVACStart.Value = Null
                    Me.txtVACEnd.Value = Null
                    Me.txtTOStartDate.Value = Null
                    Me.txtTOEndDate.Value = Null
                    Me.lstVACReason.Value = Null
                    Me.txtVACComments.Value = Null
                    Me.txtIssueKey = Null
    End If
    ErrorHandler_Exit:
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
    ErrorHandler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
        Resume ErrorHandler_Exit
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  2. Validate Dates
    By pervezjan in forum Access
    Replies: 1
    Last Post: 07-07-2015, 10:46 AM
  3. validate dates with checkboxes
    By princess12 in forum Access
    Replies: 2
    Last Post: 05-14-2015, 11:40 AM
  4. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  5. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums