View Single Post
  #1 (permalink)  
Old January 3rd, 2006, 02:56 PM
dbartelt dbartelt is offline
Authorized User
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with summing calculated fields on a report

Hi All,

I am still working on my “Aged Receivables” Report. The report is a continuous report that is grouped by the “Customer Name”. All works fine except for summing each customers aging category.

The control source for the report is a query, SELECT DISTINCTROW tblCustomers.CustomerID, tblCustomers.CustomerName, tblCustomers.AddressLine1, tblCustomers.City, tblCustomers.State, tblCustomers.ZipCode, tblCustomers.Contact, tblCustomers.Telephone1, tblCustomers.Extension1, tblInvoices.InvoiceID, tblInvoices.InvoiceNumber, tblInvoices.CustomerID, tblInvoices.InvoiceDate, tblInvoices.DueDate, tblInvoices.InvoiceBalance, tblInvoices.InvoicePaid
FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID = tblInvoices.CustomerID
WHERE (((tblInvoices.InvoicePaid)=False));

I would like to sum the four fields in the detail section.
They are:

0-30Days = Control Source =IIf(DateDiff("d",[InvoiceDate],Date())<=30,[InvoiceBalance],0)

31-60Days = Control Source =IIf(DateDiff("d",[InvoiceDate],Date()) Between 31 And 60,[InvoiceBalance],0)

61-90Days = Control Source =IIf(DateDiff("d",[InvoiceDate],Date()) Between 61 And 90,[InvoiceBalance],0)

Over91Days = Control Source =IIf(DateDiff("d",[InvoiceDate],Date())>=91,[InvoiceBalance],0)

I then put a text field in both the CustomerID Footer and in the CustomerName Footer and placed =Sum([InvoiceBalance]) as the control source. The field is blank when it is viewed.

I them placed =Sum(Nz([InvoiceBalance])) as the control source and $0.00 appears even when the field in the detail section displays an amount.

Can calculated fields in a report be summed? If so how or where do I place the “sum” field.

I would appreciate any help with this problem.

D. Bartelt
D. Bartelt
Reply With Quote