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

Your average calculation is "[BlockGapSum]/[ItemCount]", but you just told
us that the control that holds the total gap is called "TotalGap", so change
the average calculation to [TotalGap]/ItemCount].

That *should* do it! :-)

Lambert

> -----Original Message-----
> From:	Christopher Hawkins [SMTP:clh at christopherhawkins.com]
> Sent:	Friday, February 27, 2004 1:16 PM
> To:	accessd at databaseadvisors.com
> Subject:	[AccessD] SUM-ing results of a DateDiff Operation in a
> report?
> 
	<snip> 

> THE REPORT:
> 
> 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"
	<snip>

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