Solved

Access Workday Query

Posted on 2008-10-09
21
489 Views
Last Modified: 2013-11-27
I have a table that contains a CreationDate.  This is the date the item was created.  I currently produce metrics on how many items are aging 0-2, 3-13, and 14+ days.  I do this in seperate queries using statements like "Between Date()-2 and Date-14".  I then do a seperate query for each which feeds me the totals.  This has been working extremely well.... however, now I need the same queries to return only the workday (i.e. records created Monday - Friday) records. I am not worried about holidays, just that the records are M-F.  For instance if today is Monday, the 0-2 day query would need to return all the records that were created on Thursday and Friday versus Saturday and Sunday.  

I've been searching high and low and there are a lot of Modules created that count the number of workdays, but none that seem to return all records between the days.  I am sure the code could be modified, but know nothing about VBA coding.  Need this pretty quickly so any and all help would be greatly appreciated.

JJ
0
Comment
Question by:tjm5508
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Have a look at the UDF I posted here: http://www.experts-exchange.com/Q_22904290.html#20108620

You can use this to identify, say, 0-2 day items this way:

SELECT *
FROM SomeTable
WHERE WorkingHrs([CreatedDate], Date(), 0, 1, 23456) <= 48
0
 
LVL 8

Expert Comment

by:fabriciofonseca
Comment Utility
you can use the weekday function:

weekday(Date)

Result:
1 = Sunday, 2 = Monday... 7=Saturday, so you filter only from 2 y 6.

Regards
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Try this out:

Jim
Function NetWorkdays(Startdate As Date, EndDate As Date) As Double
 

    On Error GoTo err_NetWorkdays

    Dim retval As Double

    If Not IsNull(Startdate) Or Not IsNull(EndDate) Then

        If Startdate > EndDate Then

            Do While EndDate <= Startdate

                If Weekday(EndDate) = 1 Or Weekday(EndDate) = 7 Then

                    EndDate = EndDate + 1

                Else

                    retval = retval + 1

                    EndDate = EndDate + 1

                End If

            Loop

                

        Else

            Do While Startdate <= EndDate

                If Weekday(Startdate) = 1 Or Weekday(Startdate) = 7 Then

                    Startdate = Startdate + 1

                Else

                    retval = retval + 1

                    Startdate = Startdate + 1

                End If

            Loop

                

        End If

            If IsError(retval) Then

            Else

                NetWorkdays = retval - 1

            End If

    Else

        NetWorkdays = 0

    End If

    Exit Function

err_NetWorkdays:

    NetWorkdays = 0

End Function

Open in new window

0
 

Author Comment

by:tjm5508
Comment Utility
matthewspatrick:

I am not sure how counting hours helps me, although I may be missing something.  And if it will return all records for the last two days, can it also be adapted for 3-13, and all those greater than 14 days?
0
 

Author Comment

by:tjm5508
Comment Utility
fabriciofonseca:

Thanks.  I tried using the weekday, and it did return the numbers.  I could even filter out the Saturdays and Sundays.  However when I went to run my query it still looks for the weekdays and not just workdays.   i.e. "Between Date() and Date()-2"  Still covers weekends...........
0
 

Author Comment

by:tjm5508
Comment Utility
jmoss111:

Thanks... I was able to put your code into a module, but not sure how to call it in a query.  When I try running it like this in my query "Expr1: Networks([dateNorm])" I get an Undefined Function "Networks" in Expression" error.  When I try running it like this "Expr1: [Networks]", I get a dialog box for a parameter but not sure what to put in it.  Whatever I enter, it puts that for each row in the query...... again I am a beginner using modules, so I am probably missing something critical here.....
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
tjm5508 said:
>>I am not sure how counting hours helps me, although I may be missing something.

I converted hours to days.  For example, in the query example, I used <=48 hours (ie 2 days)
0
 

Author Comment

by:tjm5508
Comment Utility
OK.  Thanks.  Again I am a beginner with the module stuff.  I created the module, but not sure how to call it in my query.  When I try I get the Undefined Function" error.  Not sure where I am going wrong...
0
 

Author Comment

by:tjm5508
Comment Utility
I have created a master table of M-days which has a running number from in it.  Is it possible to create a function that:

1. will find the number that is associated with DATE()
2. Find a number that is 2 numbers less that the number returned in number one.
3. Will then pull all the records between those 2 numbers from another table?
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
In your query Add a column MyWorkDays:NetWorkdays(your start date , Your End Date ).

In the ( first parameter could be a column name that has your start date, second parameter could be another column or Date() which would be today.)

Jim
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
I'm attaching an example Access 2003 db.

Jim
AR4Consolidated.mdb
0
 

Author Comment

by:tjm5508
Comment Utility
Jim

Thanks so much! This workds great.  Was not what I had pictured the results to be, but once I ran the module and query against my data, I could cleary see how to use it!  It actually is much easier and cleaner than I envisoned, which means it will be a lot less work for me to implement, which is always a good thing!

Thanks again, going the extra bit with having an example really made the difference.

JJ
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
JJ, I'm glad that I could help out.

Jim
0
 

Author Comment

by:tjm5508
Comment Utility
Jim

Everything works great until I try to export the results to an Excel Spread Sheet.  I get a "Function not defined in expression Error"  Is there a fix for this, as I really need to export the results to Excel in an Autoupdate link?

JJ
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Open another query and use the query with the function as input to the new query. That way there is no direct reference to the function in the query that you're linking to in Excel. Actually that isn't exporting to Excel which would cause no problem; you're pulling from the datasource in Excel which is more like an import...
0
 

Author Comment

by:tjm5508
Comment Utility
Thanks... I ended up making the query a make table query and pulled the data from there.  I will give the query on query a try as that will be a little cleaner.

JJ
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
JJ,

How is everything working now?

Jim
0
 

Author Comment

by:tjm5508
Comment Utility
Everything is working great!  I appreciate all the help and followup.

JJ
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 300 total points
Comment Utility
I just wondered if everything was OK with the question still open.
0
 

Author Closing Comment

by:tjm5508
Comment Utility
Outstanding solution, support, and follow-up!
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
JJ,

Thanks for the nice comment.

Have a great day!

Jim
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

9 Experts available now in Live!

Get 1:1 Help Now