Solved

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

Posted on 2007-04-06
20
262 Views
Last Modified: 2008-02-01
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!
0
Comment
Question by:Andreamc
  • 9
  • 9
  • 2
20 Comments
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18865660
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
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18865709
> 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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18866212
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
 

Author Comment

by:Andreamc
ID: 18866229
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18866265
Can you post [pub id query] sql?
Are there groupings by job, or does the query limit it to one job?


0
 

Author Comment

by:Andreamc
ID: 18866311
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18866370
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
 

Author Comment

by:Andreamc
ID: 18867882
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18868238
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
 

Author Comment

by:Andreamc
ID: 18868348
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
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

 
LVL 26

Expert Comment

by:jerryb30
ID: 18868350
Topic Area. Old name for Zone.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 50 total points
ID: 18870060
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
 

Author Comment

by:Andreamc
ID: 18870173
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18870196
Andrea:
Glad to help.
But I did not teach myself.  I learned from others.
jerry
0
 

Author Comment

by:Andreamc
ID: 18870217
Good point!
0
 

Author Comment

by:Andreamc
ID: 18873048
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18873186
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
 

Author Comment

by:Andreamc
ID: 18873211
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18873255
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
 

Author Comment

by:Andreamc
ID: 18873897
That's helpful...thanks for your patience and help on this. I'm very pleased.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

12 Experts available now in Live!

Get 1:1 Help Now