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

I think it you have four text boxes with calculated values, you can sum them by referring to the text boxes, and not to the record field, like:

=Sum([txtField1]+[txtField2]+[txtField3]+[txtField4])

I use this technique for other purposes and it seems to work.

HTH

mmcdonal

Thank you for your reply. I tried what you suggested and I receive a parameter box “txt0-30Days” when I open the report. The text boxes I am trying to sum are calculated fields and are not in any table. They are calculated off of the InvoiceDate. I group the report by CustomerName & CustomerID. What I need to do is sum each text box type separately.

I have tried =Sum(Nz([0-30Days])). This control shows $0.00 as the result.
I then tried =Nz([0-30Days]). This control shows the amount only for the last record in each group.

Customer A
                               [u]0-30Days</u> [u]31-60Days</u> [u]61-90Days</u> [u]Over90Days</u>
Invoice 1 $25.00
Invoice 2 $36.00
Invoice 3 $72.00


=Sum(Nz([0-30Days])) results $0.00
=Nz([0-30Days]) results $72.00

----------

Customer B
                             [u]0-30Days</u> [u]31-60Days</u> [u]61-90Days</u> [u]Over90Days</u>
Invoice 1 $18.00
Invoice 2 $65.00
Invoice 3 $53.00


=Sum(Nz([0-30Days])) results $0.00
=Nz([0-30Days]) results $53.00


=Sum([0-30Days]) results in the same type of parameter box as =Sum([txt0-30Days]) when the report is opened.

Do I need to create a new text box for running totals for each of the calculated text boxes?

I’m confused!!!!!!


D. Bartelt

Hi Ya D!

First thing I'd try is renaming your textboxes. 0-30days to LessThanThirtyDays
ThirtyOnetoSixtyDays
SixtyOnetoNinetyDays

I think Access may be trying to compute the name.

Kevin
Dartcoach

dartcoach

Or try =Sum([textBox].Value) ?

mmcdonal

Thank you both for your replies. I have tried both of them. I am still having the same problems. Either a parameter box "Sum([0-30Days].[Value])" or "Sum([LessThanThirtyDays].[Value])" appears when the report is opened, or I still get $0.00 with =Sum([LessThanThirtyDays]) or the value of the last line of the group with =Nz([LessThanThirtyDays]).

I must have a grouping problem. However, the [InvoiceBalance] text box performs like it should. It totals the amount for each customer and it gives a grand total for all invoices for all customers at the end of the report.?? :(

D. Bartelt

Dartcoach & mmcdonal,

Thank you for yur help. I believe I figured it out. I created a CustomerName footer and placed text boxes that are running sums both "Over Group" and "Over All". I then reference those in both the CustomerID and the report footers. All of the calculations appear to be providing both the "groups" totals as well as the Report total.

I appreciate your guys help. Thanks

D. Bartelt