[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Disregard weekends in a date query

Posted on 2008-06-16
3
Medium Priority
?
326 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

656 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