Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prior day Now()-1 excluding weekend (2000)

    Anyone ever wanted to fill a date field with the prior weekday (excluding weekend days)?

    Thanks,
    Howard

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Prior day Now()-1 excluding weekend (2000)

    Yes - it basically involves calculating the weekday to see if it is a weekend, and if it is, subtracting either 1 or 2 more depending on whether the Now()-1 is Saturday or Sunday. Use the expression builder or help to see how Weekday() works.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prior day Now()-1 excluding weekend (2000)

    It's tempting to reply "Yes" and leave it at that <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    Put this function in a standard module:

    ' Returns the last workday before aDate

    Function PreviousWorkday(aDate As Variant)
    Dim d As Date
    If IsNull(aDate) Then
    PreviousWorkday = Null
    Exit Function
    End If
    d = DateValue(aDate) - 1
    Do While WeekDay(d) = vbSunday Or WeekDay(d) = vbSaturday
    d = d - 1
    Loop
    PreviousWorkday = d
    End Function

    To get the prior workday to today, use PreviousWorkday(Date())
    In an expression (e.g. the control source of a text box), this would become
    =PreviousWorkday(Date())

    Note: this function doesn't take holidays into account.

  4. #4
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prior day Now()-1 excluding weekend (2000)

    Thanks for the code sample Hans - I started thinking about the holidays too. I came up with

    Function PriorWorkDay()

    Dim db As Database
    Dim holidays As Recordset
    Dim test As Date
    Dim increment As Integer

    Set db = CurrentDb
    Set holidays = db.OpenRecordset("tblholidays")
    holidays.Index = "PrimaryKey"

    increment = 1
    Do While increment
    test = Date - increment
    If Weekday(test) <> 1 And Weekday(test) <> 7 Then 'if not a weekend
    holidays.Seek "=", test
    If holidays.NoMatch Then 'not a holiday
    PriorWorkDay = test
    GoTo PriorWorkDay_Exit
    End If
    End If
    increment = increment + 1
    Loop

    PriorWorkDay_Exit:
    holidays.Close
    db.Close
    Exit Function

    PriorWorkDay_Error:
    MsgBox Err & " " & Error
    Resume PriorWorkDay_Exit

    End Function

    My tblHolidays table has two fields, Date and Description. I have an index called "PrimaryKey" on the Date field. This seems to also work, and bypasses the holidays.

    Thanks,

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •