Sum Controls. 1

[lars7]

Hi Guys,
I want to sum all the controls in my subForm but if there is a 0 then it won't show anything. Here is the code I am using in an unbound text box:

=Sum([HerSun])+Sum([DailyRec])+Sum([Met])+Sum([WoM])+Sum([JobCen])+Sum([VacBul])+Sum([Jou])+Sum([JouWeb])+Sum([NHSInt])+Sum([OpDa])+Sum([RefBy])+Sum([Sh])+Sum([Cam])

I have this in the bound controls:

=IIf([HerSun] Is Null,0,[HerSun])

Can anyone see where I am going wrong.

 

[TheAceMan1]

How are ya lars7 . . .

Add a question mark [purple]?[/purple] to the [blue]Tag[/blue] property of all the controls involved in the sum. Next copy/paste the following function to the code module of the form.

[blue]Public Function AllSum()
   Dim ctl As Control, hldSum
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         hldSum = hldSum + Nz(ctl)
      End If
   Next
   
   AllSum = hldSum

End Function[/blue]

The change you formula to

[blue] = AllSum()[/blue]

See Ya! . . . . . .

 

[Remou]

I think you will need some Nz in your sum statement. This may interest you:
Count checked Check Boxes on a report
thread705-361544

 

[lars7]

Thanks guys for the replys

I tried yours and it works great.

 

[lars7]

Hi,
I have tried to install the same code in a report and I am getting an error message saying "error in loading DLL" am I missing a reference in the library:

Public Function AllSum()
Dim ctl As Control, hldSum

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
hldSum = hldSum + Nz(ctl)
End If
Next

AllSum = hldSum

End Function

 

[lars7]

Sorry guys false alarm, it had something to do with me logging in to another computer that didn't have office 2003 (I think). It's fine now.