Formula for Group Footer (1 Viewer)

Tophan

Registered User.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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.

Can someone please help me with this formula? I tried
Code:
=Sum([TotalTanks])
but that's not working.
 

June7

AWF VIP
Local time
Today, 04:44
Joined
Mar 9, 2014
Messages
4,450
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
Hi. Just curious, what does the *1 (multiply by 1) do?
 

Tophan

Registered User.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
Hi. Just curious, what does the *1 (multiply by 1) do?
Actually...looking at the formula *1 does nothing :unsure: 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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
Actually...looking at the formula *1 does nothing :unsure: 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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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
Local time
Today, 04:44
Joined
Mar 9, 2014
Messages
4,450
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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
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.
Local time
Today, 08:44
Joined
Mar 27, 2011
Messages
326
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
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
Local time
Today, 05:44
Joined
Oct 29, 2018
Messages
19,164
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.
 

Users who are viewing this thread

Top Bottom