How do I calculate number of days between 1st and last record in a report?

Using MS Access, I've built a database for our Publishing shop that each artist uses to track the amount of time spent designing & revising each of their jobs. A report can be generated for a each job that lists every date the job was worked on, and the number of minutes it took each time. While I have figured out how to get the total number of hours a job took, what I haven't been able to do is calculate the total number of days this job was worked on (i.e, the job may be in our shop for 10 days, the artist may have worked on it 4 different times, for a total of 2 hours, over the course of the 10 days). Would I use the DateDiff, and how would I indicate subtracting the most recent date from the earliest date generated in the report, to get the total number of days, and I only want to include "business days" (Monday to Friday)? Thanks!
AndreamcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ImoutwestCommented:
Dim dMaxDate as Date, dMinDate as Date, iNumDays as Integer

dMaxDate = DMax("FieldName", "TableName", "Criteria")
dMinDate = DMin("FieldName", "TableName", "Criteria")

iNumDays = DateDiff("d", dMaxDate, DMinDate)

This code would be placed in the on format event for the report, might be simpilier to use Max/Min if that is specific enough for each report.

0
ImoutwestCommented:
> and I only want to include "business days" (Monday to Friday)
Missed this part, that becomes much more complicated (at least for me).
I've read a lot of discussion (here on EE) and haven't seen a simple solution for handling Weekends...
0
jerryb30Commented:
Function workdays(mday As Date)
If WeekDay(mday) = 1 Then
workdays = False
ElseIf WeekDay(mday) = 7 Then
workdays = False
Else: workdays = True
End If

End Function
Function wdayc(sday, eday As Date)
Dim vdays As Integer
For vdays = 1 To DateDiff("d", sday, eday)
If workdays(sday) = True Then
wdayc = wdayc + 1
End If
Next vdays
End Function

Can handle workdays, but not holidays.

So = wdayc(DMin("FieldName", "TableName", "Criteria"),DMax("FieldName", "TableName", "Criteria")) might work for you.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AndreamcAuthor Commented:
I'm afraid I'm not very sophisticated at this, so will need a little more direction, I'm afraid. Simpler is better for me, so if using Max/Min is simpler and an option, I'd go that route. The report is drawn from a record source called "Pub ID Query", which is based on a table called "Timesheet". So to set things up, do I go into the report in design mode, right mouse click on the Section that has the "Date" field (called "Detail); go down to "Properties", click on the 3 dots beside "On format", select "Code Builder", click okay, which results in:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
End Sub
Then do I enter your code, changing "Fieldname" to "Date", and "TableName" to "Pub ID Query"? I don't know what to put for criteria, as there is none indicated in my Pub ID Query. So...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dMaxDate as Date, dMinDate as Date, iNumDays as Integer
dMaxDate = DMax("Date", "Pub ID Query", "Criteria??")
dMinDate = DMin("Date", "Pub ID Query", "Criteria??")
iNumDays = DateDiff("d", dMaxDate, DMinDate)
End Sub

I've tried the above as outlined, and I get a compile error "Ambiguous name detected "Project_Code_History_Click
Thanks,
0
jerryb30Commented:
Can you post [pub id query] sql?
Are there groupings by job, or does the query limit it to one job?


0
AndreamcAuthor Commented:
I hope this is what you asked for (I went into "design mode" on the "pub id query", and opened it in SQL view (and yes, the report just lists the dates, activities and hours for one job):

SELECT Timesheet.Date, [Project Codes].[Unique Project #], [Project Codes].[Project Code], [Project Codes].Title, [Project Codes].[Job Type], Timesheet.[Work Unit #], Timesheet.Hours, Timesheet.Rate, [Hours]*[Rate] AS Charge, [Hours]*[Rate]*0.07 AS GST, [Job Status].[Job Status], [Charge]+[GST] AS [Daily Total], Timesheet.Comments, [Project Codes].[Pub ID]
FROM [Project Codes] INNER JOIN ([Job Status] INNER JOIN Timesheet ON [Job Status].[Job Status] = Timesheet.[Job Status]) ON [Project Codes].[Unique Project #] = Timesheet.[Unique Project #]
WHERE ((([Project Codes].[Pub ID])=[Enter Pub ID]))
ORDER BY Timesheet.Date, [Project Codes].[Unique Project #];
0
jerryb30Commented:
OK. Just what we needed.
So the report has available as a field [date]?
create a textbox on the report whose rowsource is
=wdayc(dmin("[date]", "[pub id query]"), dmax("[date]", "[pub id query]"))

The two functions should be placed in a new module.
 
0
AndreamcAuthor Commented:
Thanks for your assistance...I think I'm out of my depth here. Up to now, I've relied on macros, etc., but clearly I need to get up to speed on rudimentary coding...any beginner books you could recommend?
Andrea
0
jerryb30Commented:
I honestly learned all of my coding from looking at other's examples here.  The are a couple of questions out there on recommended vba books.  Do a search for beginner vba book, focusing on Access TA.

Can you  post you data base with sample data at www.ee-stuff.com?
0
AndreamcAuthor Commented:
I've uploaded the database, and the link is https://filedb.experts-exchange.com/incoming/ee-stuff/3120-Design-Job-Tracking.mdb.zip
As noted, it's not a true "zipped" file, I just added "zip" onto the end of the filename, so just remove ".zip" and it will revert to a ".mdb" file. I've noted the report name, and the Pub ID to enter, to access the report in question. Thanks so much for offering to have a look at my database! BTW, what does "TA" stand for, as in Access TA?
Andrea
0
jerryb30Commented:
Topic Area. Old name for Zone.
0
jerryb30Commented:
I looked at your db.
The
Ambiguous name detected "Project_Code_History_Click"
error was because you had the same sub 4 or 5 times in your report.
Deleted extra ones.
Added module with working function wdayc and function workdays. (had a couple of errors.)
Added total days to your report, which uses the min and max dates in your report and the function.
=wdayc(min([date]) max([date]) )

The total days is inclusive.  16-18 Mar dates for pub id 3319 is 3 days.

uploaded to www.ee-stuff.com at
https://filedb.experts-exchange.com/incoming/ee-stuff/3121-REDesign-Job-Tracking-1-.zip
I suggest you look at it, ask questions about what is happening if you have them, rather than just taking what I did and replacing your db.
hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamcAuthor Commented:
Jerryb -- This is terrific!!! Thanks so much! I am keen to understand what you've done, so I will review your solution carefully first and follow up with any questions I have. I'm impressed that you've been able to teach yourself this stuff...
Cheers...will follow up soon...Andrea
0
jerryb30Commented:
Andrea:
Glad to help.
But I did not teach myself.  I learned from others.
jerry
0
AndreamcAuthor Commented:
Good point!
0
AndreamcAuthor Commented:
I've spent some time examining the code, and was able to successfully use the code for another report as well. A few questions:
I expected to see the code activated by the "On Format" on the detail section of the report, but there wasn't anything listed in "On Format"; how is the code associated with, and activated by, the report?
When I applied the code to a second report I have, I did it in what is probably a labour-intensive way: opened the 2nd report in design mode, clicked on the "Code" icon in taskbar, and copied the code over; I did this twice for the two pages of code. Thinking about how I can use one macro for several reports, is there an easier way to associate the module to multiple reports?
Thanks, Andrea
Trying to understand the code logic is a little beyond me at the moment, except I assume Access considers Sunday = 1, and Saturday = to 7?
0
jerryb30Commented:
Indeed, the function considers Sunday as 1 and Saturday as 7.
No need to copy the code to each report. As a function, it is as available to any report, query or form as if you were trying to use a square root function, or any of the built-in functions of Access. As long as you pass in the arguments (start date abd end date) it will work.
Adding it to other report code will only cause problems.
0
AndreamcAuthor Commented:
I followed up by removing the code from the second report, and now it asks me for "wdayc", and gives me an "error" where the number of days should be. (I added the "=wdayc(Min([date]),Max([date]))" to the footer of the second report). So just to understand things better, what do I need to do so the second report works again...is it something to do with: "As long as you pass in the arguments (start date abd end date) it will work." If it is, could you explain what that means...I figure if I can get the second report to work again, then I'll have a better understanding of how the code makes itself available to any report, query or form.
Thanks! Andrea
0
jerryb30Commented:
Consider the Datediff function. It has 3 arguments: period, date1, date2.
So datediiff("d", #1 jan 07#, #2 jan 07#) returns 1.
wdayc(#1 jan 07#, #17 mar 07#)  returns 56
0
AndreamcAuthor Commented:
That's helpful...thanks for your patience and help on this. I'm very pleased.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.