|
Subject:
|
Help with summing calculated fields on a report
|
|
Posted By:
|
dbartelt
|
Post Date:
|
1/3/2006 1:56:14 PM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/4/2006 7:48:46 AM
|
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
|
|
Reply By:
|
dbartelt
|
Reply Date:
|
1/4/2006 9:58:13 AM
|
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
0-30Days 31-60Days 61-90Days Over90Days
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
0-30Days 31-60Days 61-90Days Over90Days
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
|
|
Reply By:
|
dartcoach
|
Reply Date:
|
1/4/2006 10:00:13 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/4/2006 10:17:47 AM
|
Or try =Sum([textBox].Value) ?
mmcdonal
|
|
Reply By:
|
dbartelt
|
Reply Date:
|
1/4/2006 1:16:21 PM
|
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
|
|
Reply By:
|
dbartelt
|
Reply Date:
|
1/4/2006 2:33:35 PM
|
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
|
