Link to home
Create AccountLog in
Avatar of gfelton
gfelton

asked on

If Date is a weekend - make it a weekday

I have an Access form.  The user types in a startdate.  The below is part of the code I use on an afterupdate - - it  looks at the date and calculates a due date if the due date falls on a Saturday I need the due date to be a Friday or if the due date falls on a Sunday I need the due date to be a Sunday.  The date calculates correctly, but if the due date is a Saturday or Sunday it doesn't work with the function I have below.  How do I modify the function to make that happen.

Private Sub LogShowStartDate_AfterUpdate()
    Dim rec As Recordset
    Dim sql As String
   
    sql = "select * from [TblNewChecklist] where showid = " & Me.Show_ID
    Set rec = CurrentDb.OpenRecordset(sql)
    Do While Not rec.EOF
        rec.Edit
         
        Select Case rec!number
            Case 1
                rec!DateDue = getWeeksDate(Me.LogShowStartDate, -12)
            Case 2
                rec!DateDue = getWeeksDate(Me.LogShowStartDate, -10)
            Case 3, 4, 5, 12, 14
                rec!DateDue = getWeeksDate(Me.LogShowStartDate, -9)
                             .................


Function getWeeksDate(inDate As Date, inWeeks As Integer) As Date
    getWeeksDate = DateAdd("ww", inWeeks, inDate)
    If day(getWeeksDate) = "Saturday" Then
        getWeeksDate = DateAdd("d", -1, getWeeksDate)
    End If
    If day(getWeeksDate) = "Sunday" Then
        getWeeksDate = DateAdd("d", 1, getWeeksDate)
    End If
End Function
Avatar of puppydogbuddy
puppydogbuddy

Try changiing your syntax for the statements shown below and see if it helps.

If Format(day(getWeeksDate) ,"dddd") = "Saturday" Then

If Format(day(getWeeksDate) ,"dddd") = "Sunday" Then
Avatar of gfelton

ASKER

Sorry, it didn't work.
Avatar of gfelton

ASKER

Any more suggestions!
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Also, you could reduce your main function a litte:

Private Sub LogShowStartDate_AfterUpdate()
    Dim rec As Recordset
    Dim sql As String
    Dim intWeeks As Integer
   
    sql = "select * from [TblNewChecklist] where showid = " & Me.Show_ID
    Set rec = CurrentDb.OpenRecordset(sql)
    Do While Not rec.EOF
        Select Case rec!number
            Case 1
                intWeeks = -12
            Case 2
                intWeeks = -10
            Case 3, 4, 5, 12, 14
                intWeeks = -9
        End Select
        If intWeeks <> 0 Then
          rec.Edit
          rec!DateDue = getWeeksDate(Me.LogShowStartDate, -intWeeks)
          rec.Update
        End If

/gustav