Count number of fields on a form that don;t have a "0" in them

On a form I have 10 numeric fields... text1, text2, text3, text4, text5, text6, text7, text8, text9, text10.

Ten in another text box on the form I want to COUNT the number of above fields that DO NOT have a "0" in them.  So, for example, if...

text1 = 3
text2 = 0
text3 = 0
text4 = 0
text5 = 7
text6 = 23
text7 = 0
text8 = 92
text9 = 3
text10 = 0

Then the result in the counting field would be 5 since only five of the above fields have a value greater than 0.

What would the control code be for the counting field?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This looks like a repeating group and so should be implemented as 10 rows in a many-side table and in that case you would use a query -- Select Count(*) As NonZeroCount From YourTable Where SomeField <> 0;

To "count" the instances of non zero values, place an expression similar to the following in the ControlSource of a textbox.

=IIf(Text1 = 0, 0, 1) + IIf(Text2 = 0, 0, 1) + IIf(Text3 = 0, 0,1), ....

Or if the controls might be null

=IIf(Nz(Text1,0) = 0, 0, 1) + IIf(Nz(Text2,0) = 0, 0, 1) + IIf(Nz(Text3,0) = 0, 0,1), ....
Rey Obrero (Capricorn1)Commented:
create this function in the Form's module
then place this in the current event of the form or in the after update of each textbox, depending on the way you are using the form


Function CountNONZero() as integer
dim i as integer, vCount as integer
for i = 1 to 10
   if me("text" & i) = 0 then
      vcount=vcount + 1
  end if

end function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Here is my code but I'm getting an error when I compile... "Member already exists in an object module from which this object module derives".  Then the line "Function CountNONZero() As Integer" is highlighted in blue.

Function CountNONZero() As Integer

Dim i As Integer, vCount As Integer
    For i = 1 To 10
        If Me("Text" & i) = 0 Then
        vCount = vCount + 1
    End If
    CountNONZero = vCount

 End Function

Private Sub Form_Current()

    Me.txtCount = CountNONZero()

End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
I like Rey's technique, (and strongly recommend Pat's recommendation of using a table with 10 records and a subform for entering the data) but another technique would be to put the following in the ControlSource of the countNonZero textbox:

ControlSource: = iif(NZ(me.text1, 1) = 0, 0 , 1) + iif(NZ(me.text2, 1) = 0, 0 , 1) +
  iif(NZ(me.text3, 1) = 0, 0 , 1) + iif(NZ(me.text4, 1) = 0, 0 , 1) +  iif(NZ(me.text5, 1) = 0, 0 , 1) + iif(NZ(me.text6, 1) = 0, 0 , 1) +   iif(NZ(me.text7, 1) = 0, 0 , 1) + iif(NZ(me.text8, 1) = 0, 0 , 1) +  iif(NZ(me.text9, 1) = 0, 0 , 1) + iif(NZ(me.text10, 1) = 0, 0 , 1)

Theoretically, this will automatically update itself whenever you make a change in any of those other controls, but you might have to issue a requery in the AfterUpdate event of each of the controls (Text1-Text10).
Rey Obrero (Capricorn1)Commented:
were you able to correct the error?
SteveL13Author Commented:
Yes.   I had named the field me.txtCount with a typo.  Once I fixed it all was fine.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.