Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 3rd, 2006, 02:56 PM
Authorized User
 
Join Date: Apr 2005
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
 
Old January 4th, 2006, 08:48 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 4th, 2006, 10:58 AM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 4th, 2006, 11:00 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old January 4th, 2006, 11:17 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old January 4th, 2006, 02:16 PM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 4th, 2006, 03:33 PM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create calculated fields MacDevv C# 3 August 16th, 2006 09:38 AM
Nested Calculated Fields in Queries SerranoG Access 0 August 14th, 2006 10:04 AM
Storing Calculated Fields dearnne Access 3 September 2nd, 2004 05:30 PM
Storing Calculated Fields dearnne Access VBA 1 August 30th, 2004 10:54 AM
Summing the Results of Two Calculated Fields CloudNine Access 1 December 24th, 2003 12:05 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.