[Diane Herron]

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!

[cristobal03]

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]

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

[cristobal03]

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.