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

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.

> 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...

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.

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,

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

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 #];

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.
 

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

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?

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

Topic Area. Old name for Zone.

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

Andrea:
Glad to help.
But I did not teach myself.  I learned from others.
jerry

Good point!

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?

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.

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

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

That's helpful...thanks for your patience and help on this. I'm very pleased.