Formula for Group Footer

[Tophan]

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

=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

=Sum([TotalTanks])

but that's not working.

 

[June7]

Aggregate functions must reference fields, not controls.

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

Why multiply by 1?

 

[theDBguy]

Hi. Just curious, what does the *1 (multiply by 1) do?

 

[Tophan]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Okay, try moving the Textbox into the Details section, just to check again.

It is working in the details section

 

[Tophan]

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]

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 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.