There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
 
Business Applications
Tag Cloud
access backup blue screen boot bsod computer connection crash dell desktop drivers email error freeze freezing graphics card hard drive hardware help ! internet internet explorer itunes laptop malware mcafee motherboard mouse network printer problem registry router server spyware startup system restore toshiba trojan usb video virus vista website wifi windows windows 7 windows 7 32-bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Access Report - Summing the results of a Detail text box

Closed Thread
 
Thread Tools
Diane Herron's Avatar
Member with 81 posts.
 
Join Date: Dec 2000
Location: Cleveland, Ohio, USA
15-Feb-2006, 11:46 AM #1
Access Report - Summing the results of a Detail text box
I created a text box named "DaysToApprvd" in the detail section that says "=IIf([ApprvdDecld]="Approved",[DateApprvdDeclined]-[DateRcvd],""). This, of course, tells me the number of days from which it was received to which it was approved. I get a list of numbers, for example:

2
5
7
2
I would now like to sum these numbers in the group footer so that I would see a total of 16 (the sum of the above numbers). I thought "=Sum([DaysToApprvd])" would do it. However, I get the message "The Microsoft Jet database engine does not recognize '[DaysToApprvd]' as a valid field name or expression."

Help!
__________________
Diane Herron
cristobal03's Avatar
Distinguished Member with 2,996 posts.
 
Join Date: Aug 2005
Experience: Advanced
15-Feb-2006, 12:20 PM #2
Sum is a scalar function used in SQL queries. What you want is a running sum, which is particular to reports. In the group footer textbox control with which you wish to display the sum, do the following:
  • Open the control's Properties window.
  • Under the Data tab, set the Running Sum property to Over Group.
  • Again, under the Data tab, set the Control Source property to equal the name of the group control you want to sum. In your case,
    =[DaysToApprvd]
  • Save your report and preview it to see if this works for you.
I'm no great shakes with reports, so this is just a guess. But give it a try on a copy of your report.

chris.
Diane Herron's Avatar
Member with 81 posts.
 
Join Date: Dec 2000
Location: Cleveland, Ohio, USA
15-Feb-2006, 02:23 PM #3
Angry Access Report - Summing the results of a Detail text box
That idea appears to be trying to work. However, it only adds the last number if there is a last number. In other words, if I have a column of numbers:

2
4
3
6
It will give me an answer of 6. Of course, I should get an answer of 15. If I have a column of numbers:
2
4
3
(blank)
It will give no answer. I should get an answer of 9.

Also, it makes no difference if I set it to Over Group or Over All, I get the same answers.

Grrrr
__________________
Diane Herron
cristobal03's Avatar
Distinguished Member with 2,996 posts.
 
Join Date: Aug 2005
Experience: Advanced
15-Feb-2006, 02:59 PM #4
Well, if you only have one group, you would get the same answer. However, I think this may be a data type issue. I imagine those fields [DateApprvdDeclined] and [DateRcvd] are Date data types, yes? Somewhere in there, you're having an implicit type conversion from a number data type to text. That is, the FalsePart of your IIf is a zero-length string instead of a number. That shouldn't necessarily cause problems, but it might.

Like I said, though, I'm no good with reports. Obviously it's pulling the last value only instead of the entire group. Is there any chance you could upload a dummy copy?

chris.
Closed Thread

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.

Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 02:50 AM.
Copyright © 1996 - 2010 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.