Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 3rd, 2006, 02:56 PM
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
  #2 (permalink)  
Old January 4th, 2006, 08:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old January 4th, 2006, 10:58 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
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
Reply With Quote
  #4 (permalink)  
Old January 4th, 2006, 11:00 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #5 (permalink)  
Old January 4th, 2006, 11:17 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
Reply With Quote
  #6 (permalink)  
Old January 4th, 2006, 02:16 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
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
Reply With Quote
  #7 (permalink)  
Old January 4th, 2006, 03:33 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:45 PM.


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