tjm5508
asked on
Access Workday Query
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
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
you can use the weekday function:
weekday(Date)
Result:
1 = Sunday, 2 = Monday... 7=Saturday, so you filter only from 2 y 6.
Regards
weekday(Date)
Result:
1 = Sunday, 2 = Monday... 7=Saturday, so you filter only from 2 y 6.
Regards
Try this out:
Jim
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
ASKER
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?
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?
ASKER
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...........
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...........
ASKER
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.....
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.....
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)
>>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)
ASKER
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...
ASKER
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?
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?
In your query Add a column MyWorkDays:NetWorkdays(you r 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
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
ASKER
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
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
JJ, I'm glad that I could help out.
Jim
Jim
ASKER
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
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
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...
ASKER
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
JJ
JJ,
How is everything working now?
Jim
How is everything working now?
Jim
ASKER
Everything is working great! I appreciate all the help and followup.
JJ
JJ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding solution, support, and follow-up!
JJ,
Thanks for the nice comment.
Have a great day!
Jim
Thanks for the nice comment.
Have a great day!
Jim
You can use this to identify, say, 0-2 day items this way:
SELECT *
FROM SomeTable
WHERE WorkingHrs([CreatedDate], Date(), 0, 1, 23456) <= 48