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-