# Formula for Group Footer (1 Viewer)

#### Tophan

##### Registered User.
Hello

I have a report showing the dates various activities on a project occurred. We are installing water tanks so I have a date field named TankDate which is the date the tank (or tanks) are installed at a particular property. I also added a field named TotalTanks with the following formula
Code:
``=IIf([TankDate] Is Null," ",[CountOfSystem]*1)``

This counts the number of tanks to be installed per property (FYI - if a property is to receive 2 tanks they are installed on the same day).

The properties are categorized and grouped as Households, Farms, Schools, Polyclinics, Community Tanks.

What I would like to do in the group footer for each category is give the total number of tanks installed.

Code:
``=Sum([TotalTanks])``
but that's not working.

#### June7

##### AWF VIP
Aggregate functions must reference fields, not controls.

=Sum(IIf(IsNull([TankDate]),0,[CountOfSystem]*1)

Why multiply by 1?

#### theDBguy

##### I’m here to help
Staff member
Hi. Just curious, what does the *1 (multiply by 1) do?

#### Tophan

##### Registered User.
I tried entering the above formula but when I run the report the message box "Enter parameter value?" for TankDate pops up and all the results are 0

#### Tophan

##### Registered User.
Hi. Just curious, what does the *1 (multiply by 1) do?
Actually...looking at the formula *1 does nothing I just took it out and the formula per record still works. Just can't get the sum per group to work

#### theDBguy

##### I’m here to help
Staff member
Actually...looking at the formula *1 does nothing I just took it out and the formula per record still works. Just can't get the sum per group to work
Try changing your Calculated Column to use this:

=IIf([TankDate] Is Null,Null,[CountOfSystem])

Hope that helps...

#### Tophan

##### Registered User.
Thanks.

The formulas per record are working just fine and returning the correct results but I can't get a subtotal in the group footer which is very important for this report. I need to show the total number of tanks installed per category.

#### theDBguy

##### I’m here to help
Staff member
Thanks.

The formulas per record are working just fine and returning the correct results but I can't get a subtotal in the group footer which is very important for this report. I need to show the total number of tanks installed per category.
Did you try what I suggested? Please let us know. Since we don't have a copy of your db, we have to ask you to try some possible things to isolate the actual problem.

#### Tophan

##### Registered User.
Yes. I changed the calculated column to =IIf([TankDate] Is Null,Null,[CountOfSystem]). And the total per record is correct.

I named that [TotalTanks]

I then tried to get the sum of the [TotalTanks] in the group footer but I keep being prompted to enter parameter value for [TotalTanks]

I've tried:
=Sum([TotalTanks]
=Sum(IIf([TankDate] Is Null,Null,[CountOfSystem]))

But neither are working

#### June7

##### AWF VIP
Reports can be weird about calculations referencing fields in RecordSource. TotalTanks is a textbox, not a field.

If you want to provide db for analysis, follow instructions at bottom of my post.

#### Tophan

##### Registered User.
The database is based on candidate contact info - names, addresses and phone numbers (in Barbados). If I upload the DB could you please delete it after analysing it?

#### theDBguy

##### I’m here to help
Staff member
Yes. I changed the calculated column to =IIf([TankDate] Is Null,Null,[CountOfSystem]). And the total per record is correct.

I named that [TotalTanks]

I then tried to get the sum of the [TotalTanks] in the group footer but I keep being prompted to enter parameter value for [TotalTanks]

I've tried:
=Sum([TotalTanks]
=Sum(IIf([TankDate] Is Null,Null,[CountOfSystem]))

But neither are working
Let's try to tackle it another way. What do you get if you simply use?

=[TotalTanks]

If it's empty because the TankDate is Null, try moving to the next record until it shows something. We're just trying to see if it will show the right values.

#### Tophan

##### Registered User.
Let's try to tackle it another way. What do you get if you simply use?

=[TotalTanks]

If it's empty because the TankDate is Null, try moving to the next record until it shows something. We're just trying to see if it will show the right values.
No error message or prompt asking for parameter value but the result is blank

#### theDBguy

##### I’m here to help
Staff member
No error message or prompt asking for parameter value but the result is blank
Did you move through all the records, like I said?

#### theDBguy

##### I’m here to help
Staff member
Did you move through all the records, like I said?
Oops, sorry, please disregard. For some reason, I thought we were talking about a Form here.

#### theDBguy

##### I’m here to help
Staff member
No error message or prompt asking for parameter value but the result is blank
Okay, try moving the Textbox into the Details section, just to check again.

#### Tophan

##### Registered User.
Okay, try moving the Textbox into the Details section, just to check again.
It is working in the details section

#### Tophan

##### Registered User.
I am going to upload the DB...maybe fresh eyes can see something I am missing. But I will need to delete it as soon as possible

#### theDBguy

##### I’m here to help
Staff member
It is working in the details section
Okay, now, try switching the expression back to the original, but keep the Textbox in the Details section still.

#### theDBguy

##### I’m here to help
Staff member
I am going to upload the DB...maybe fresh eyes can see something I am missing. But I will need to delete it as soon as possible
For that short period of time, I won't be able to see it, since I am only using my phone right now. Hopefully, somebody else will be able to. Otherwise, I'll be back in front of a computer in about two hours.

Replies
5
Views
325
Replies
7
Views
417
Replies
7
Views
576
Replies
9
Views
338
Replies
10
Views
561