[AccessD] SUM-ing results of a DateDiff Operation in a report?

Christopher Hawkins clh at christopherhawkins.com
Fri Feb 27 12:15:41 CST 2004


Here's some behavior that took me completely by surprise.  Perhaps
you can tell me why it's not working.  Be warned, this one's longer
than your average mail.

THE SITUATION:
I am building Yet Another Effing Billing System for a client of mine,
an outsourced IT shop that send technicians out to take care of
customers' networks.  The client wants his guys to be entering their
service visits the instant they're done, but in practice they're
getting entered a few days leter, when the guys get around to it.

So, I've been asked to build a Gap Report - a report that will
indicate the number of days between the occurrance of a service and
the date it was actually keyed into the database.

THE REPORT:

I built a query that includes (among other fields) ServiceDate (the
date the service was actually performed) and CreateDate (the date the
service was keyed into the database) from a table.  I've created a
field called Gap that uses DateDiff to get the number of days between
ServiceDate and Create date.  So:

"SELECT Technician, CreateDate, ServiceDate, DateDiff("d",CreateDate,
ServiceDate) AS Gap, ServiceDescription, CustomerID FROM Service"

Now...in the report.  I have created a report whose RecordSource is
set to my Gap query.  I created a header and footer for the
Technician, inside which is a header and footer for the ServiceDate. 
Inside that is the Detail section, which includes the CreateDate,
Technician's Name, Customer, Service Description, and most
importantly, the Gap field.

Here's where it gets interesting.

I wanted to calculate an average Gap for each Technician.  So I put a
field named ItemCount in the footer section of the Technician group
with it's ControlSource set to "Count(CustomerID)" and it works
perfectly - it gives me the number of rows in the Technician's
Details.

To get an average, I need to add up all the individual Gap figures,
so I put a field named TotalGap in the footer section of the
Technician group, with it's ControlSource set to "Sum(Gap)".  That
works fine too.

THE PROBLEM:

Now, it's time to divide to get an average.  I put a field named
AverageGap in the Technician's footer, with the ControlSource
"[BlockGapSum]/[ItemCount]".  When I run the report, this field
displays #Name!.

Usually, I only see #Name! if there is no data for a given field. 
But I have all the elements tha the AverageGap field needs - the
calculation in AverageGap simply isn't working, and I'm not sure why.

Ideas?

-Christopher-





More information about the AccessD mailing list