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

x
Solved

# Need a query to exclude weekends.

Posted on 2007-08-09
Medium Priority
1,062 Views
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
0
Question by:mandelia
• 3

LVL 17

Expert Comment

ID: 19660696
DateDiff ( "w", date1, date2, [firstdayofweek], [firstweekofyear])
0

LVL 5

Author Comment

ID: 19660725
In the scenario above what should be [firstdayofweek], [firstweekofyear]
0

LVL 17

Expert Comment

ID: 19660726
0

LVL 17

Accepted Solution

Barry Cunney earned 2000 total points
ID: 19660738
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

## Featured Post

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month18 days, left to enroll