[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

If Date is a weekend - make it a weekday

Posted on 2007-09-27
5
Medium Priority
?
384 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:gfelton
  • 2
  • 2
5 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 19976725
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
0
 

Author Comment

by:gfelton
ID: 19976915
Sorry, it didn't work.
0
 

Author Comment

by:gfelton
ID: 19977036
Any more suggestions!
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 600 total points
ID: 19977325
Modify your function like this:

Function getWeeksDate(inDate As Date, inWeeks As Integer) As Date

    Dim intOffset As Integer
   
    getWeeksDate = DateAdd("ww", inWeeks, inDate)
   
    Select Case WeekDay(getWeeksDate)
      Case vbSaturday
        intOffset = -1
      Case vbSunday
        intOffset = 1
    End Select

    getWeeksDate = DateAdd("d", intOffset, getWeeksDate)

End Function

/gustav
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 19977358
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question