Solved

Disregard weekends in a date query

Posted on 2008-06-16
3
311 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
  • 2
3 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

867 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

17 Experts available now in Live!

Get 1:1 Help Now