Solved

Disregard weekends in a date query

Posted on 2008-06-16
3
321 Views
Last Modified: 2013-11-28
I have a query that counts the number of days taken to process a particular job in our facility and reports if a particular department has exceeded allotted time. I need this query to disregard weekends.  Currently I'm using criteria thats similar to this:
Query Field: Date()
Criteria: >[Date_Entered_Into_Dept] + 3
In other words report if the Current Date is 3 days or more later than the "Date_Entered_Into_Dept".
Any ideas how I can make this ignore weekends and holidays?

0
Comment
Question by:ckarrow2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21793420
you will need a function to do that and a table for holidays.

Function getWorkDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
dtStart = vDate1
i = DateDiff("d", vDate1, vDate2) + 1
Do Until dtStart >= vDate2
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
             & dtStart & "#"))
            dtStart = dtStart + 1
            i = i - 1
    Loop
Loop
getWorkDays = i

End Function
0
 

Author Comment

by:ckarrow2
ID: 21808879
I hate to be dense... but exactly where do I put this Function?
I understand the holiday table concept tho.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 21821343
place the codes in a module.

to use in a query

select [Date_Entered_Into_Dept],getWorkdays([Date_Entered_Into_Dept],Date()) from tablex
where getWorkdays([Date_Entered_Into_Dept],Date())>3
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I am new to using JSON in SQL Server 2 57
Calculation in a Report 13 41
View SQL Count Records 3 30
STUFF function - proper use to update column? 6 16
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

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