Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Workday Query

Posted on 2008-10-09
21
Medium Priority
?
517 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 93

Expert Comment

by:Patrick Matthews
ID: 22680818
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
ID: 22680823
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
ID: 22680842
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:tjm5508
ID: 22682100
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
ID: 22682119
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
ID: 22682169
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 93

Expert Comment

by:Patrick Matthews
ID: 22682292
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
ID: 22682421
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
ID: 22682589
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
ID: 22682990
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22683290
I'm attaching an example Access 2003 db.

Jim
AR4Consolidated.mdb
0
 

Author Comment

by:tjm5508
ID: 22688702
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
ID: 22694103
JJ, I'm glad that I could help out.

Jim
0
 

Author Comment

by:tjm5508
ID: 22694195
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
ID: 22700082
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
ID: 22701698
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
ID: 22708890
JJ,

How is everything working now?

Jim
0
 

Author Comment

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

JJ
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 1200 total points
ID: 22710619
I just wondered if everything was OK with the question still open.
0
 

Author Closing Comment

by:tjm5508
ID: 31504746
Outstanding solution, support, and follow-up!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22710635
JJ,

Thanks for the nice comment.

Have a great day!

Jim
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.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

972 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