Need a query to exclude weekends.

Posted on 2007-08-09
Last Modified: 2010-02-03
Greetings Experts,
 we can check weekdays by the keyword weekday in the query.
I need a Query in access that does the following:
I have two dates. d1 and d2 in a table a.
So the query should return me the weekdays between two dates. ie it should exclude the weekends.

Fo if d1 = 09/08/2007 and d2 = 01/08/2007
then i should get 6 and not 8

if d1 = 06/07/2007 and d2 = 16/07/2007
i should return 6 and not 10
Question by:mandelia
    LVL 17

    Expert Comment

    by:Barry Cunney
    DateDiff ( "w", date1, date2, [firstdayofweek], [firstweekofyear])
    LVL 5

    Author Comment

    In the scenario above what should be [firstdayofweek], [firstweekofyear]
    LVL 17

    Expert Comment

    by:Barry Cunney
    LVL 17

    Accepted Solution

    Put the following code in a module in MS Access
    Function DateDiffW(BegDate, EndDate)
       Const SUNDAY = 1
       Const SATURDAY = 7
       Dim NumWeeks As Integer

       If BegDate > EndDate Then
          DateDiffW= 0
          Select Case Weekday(BegDate)
             Case SUNDAY : BegDate = BegDate + 1
             Case SATURDAY : BegDate = BegDate + 2
          End Select
          Select Case Weekday(EndDate)
             Case SUNDAY : EndDate = EndDate - 2
             Case SATURDAY : EndDate = EndDate - 1
          End Select
          NumWeeks = DateDiff("ww", BegDate, EndDate)
          DateDiffW= NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
       End If
    End Function

    Then create a calculated field in your query that uses this function similar to below

    CheckWeekDays: DateDiffW(#01/08/2007#,#09/08/2007#)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now