Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    San Antonio, Texas, Uganda
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclude Weekends (Access XP)

    Want to use an expression in a query to return records between dates ( e g: date() +5) that excludes weekends.
    So If it was Thursday, query said Date()+5 it would return records whose value in the date field were Friday thru following Thursday.

    Thanks.

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

    Re: Exclude Weekends (Access XP)

    Date/Time: Doing WorkDay Math in VBA and Date/Time: Calculate Number of Working Days, both on the Access Web, provide a whole series of functions that can be used for calculations involving working days.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclude Weekends (Access XP)

    I'm not sure if this will help or not:

    Taken from the Access help file
    WORKDAY
    Returns a number that represents a date that is the indicated number of working days before or after start_date, excluding weekends and specified holidays.

    If this function returns the #NAME? error value, you may need to install msowcf.dll.

    Syntax

    WORKDAY(start_date,days,holidays)

    Start_date is a date that represents the start date.

    Days is the number of nonweekend and nonholiday days before or after Start_date (negative values for days before).

    Holidays is a range of cells that represent dates to exclude from the working calendar.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Exclude Weekends (Access XP)

    That looks suspiciously like the help for an Excel worksheet function ("a range of cells"). WORKDAY is not a built-in Access function!

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclude Weekends (Access XP)

    Overly large screenshot cropped by HansV - please don't post pictures larger than 640 x 480 pixels

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Check out this screenshot (maybe there's something wrong with my help file?) <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Exclude Weekends (Access XP)

    Check Tools | References..., you probably have a reference to the Microsoft Office Web Components Function Library. This is not a standard reference.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclude Weekends (Access XP)

    Hans, that reference wasn't checked. I did the search for help in Access, not in the VBA Editor
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Exclude Weekends (Access XP)

    Which version of Access are you using? WORKDAY certainly isn't in my version of Access 2002 (XP) - the version mentioned in the subject of this thread.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclude Weekends (Access XP)

    I'm using access 2000. I wonder what's going on
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Exclude Weekends (Access XP)

    By the way, have you actually tried using WORKDAY in Access?

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclude Weekends (Access XP)

    Looking at my own post more closely <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>, I should have see the whole "worksheet" concept and bells and whistles should have gone off. Now I'm more concerned with why my help file is @#$!% up like this.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exclude Weekends (Access XP)

    Perry

    I hope it is not too late to chip in on this one but a few years ago I had to create a Postal Log for an office in my company. The problem was we do not count postal response on weekends as the clock stops. Also as we have religious/high days and observe bank holidays I wanted the database to ignore them as well. So... I wrote a function, thus. All paced in a Module:

    Option Compare Database
    Option Explicit

    Function workdays(BegDate As Variant, EndDate As Variant) As Integer
    'Note that this function does not account for holidays.

    On Error GoTo ErrLine

    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer
    Dim intWeekDays As Integer
    Dim intActWorkDays As Integer
    Dim intHolidays As Integer



    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)

    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)

    EndDays = 0
    Do While DateCnt < EndDate

    If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then

    EndDays = EndDays + 1

    End If

    DateCnt = DateAdd("d", 1, DateCnt)

    Loop

    intWeekDays = WholeWeeks * 5 + EndDays

    '''''working out holidays
    intHolidays = DCount("HolidayID", "tblHolidays", "dtObservedDate>=#" & Format(BegDate, "dd/mm/yyyy") & "# And dtObservedDate<=#" & Format(EndDate, "dd/mm/yyyy") & "#")


    intActWorkDays = intWeekDays - intHolidays
    workdays = intActWorkDays
    ExitLine:
    Exit Function

    ErrLine:
    workdays = 999
    Exit Function

    End Function

    -----------------

    I created a table called tblHolidays with fields HolidayID (PK), strHoliday and Observed date. strHoliday was the name of the holiday (ie Good Friday) and obsserved date had the date of the that holiday. I filled in loads of holiday dates (I got them from the web if I remember rightly)

    In my query running from tblPost ( it had arrivaldate and completedate as fields) I put in a new field Count: workdays([arrivaldate],[completedate]) Hey presto the count of workdays excluding weekends and holidays. If you don't want holidays included delete everything below '''''working out holidays upto ErrLine.

    Hope this helps
    Jerry

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Exclude Weekends (Access XP)

    My copy of Access 2000 also lists the Workday function in Help, without any non standard references ticked.

    But it does describe it as a workseheet function, and it does not work.
    Regards
    John



Posting Permissions

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