[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
4
Medium Priority
?
1,062 Views
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
0
Comment
Question by:mandelia
  • 3
4 Comments
 
LVL 17

Expert Comment

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

Author Comment

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

Expert Comment

by:Barry Cunney
ID: 19660726
0
 
LVL 17

Accepted Solution

by:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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…

830 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