Need a query to exclude weekends.

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
LVL 5
mandeliaAsked:
Who is Participating?
 
Barry CunneyConnect With a Mentor Commented:
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
   Else
      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#)
0
 
Barry CunneyCommented:
DateDiff ( "w", date1, date2, [firstdayofweek], [firstweekofyear])
0
 
mandeliaAuthor Commented:
In the scenario above what should be [firstdayofweek], [firstweekofyear]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.