Counting Records

[hkimpact]

Hello everyone,

I have a form with let's say, 10 numerical fields on it. The user may or may not fill all of these fields with data. I need to come up with a query that will count those records with data in them and not the null fields. Currently the values of each of these fields is 0.

This should be an easy questions. Any help would be appreciated.

 

[plog]

First, this sounds like a form issue, not a query. Second, when does this calculation take place--does the user click a button on the form? Does it occur after everytime they enter a value?

If you want to count all the non-null inputs of a form you would create a function like this:

Function getNonNullInputs()
    ' counts number of non-null inputs on form
 
ret=0
    ' default value
 
If IsNull(Me!Input1NameHere) = false Then ret=ret+1
If IsNull(Me!Input2NameHere) = false Then ret=ret+1
If IsNull(Me!Input3NameHere) = false Then ret=ret+1
...
...
...
If IsNull(Me!Input10NameHere) = false Then ret=ret+1
 
getNonNullInputs=ret
 
End Function

You would then trigger that function to run whenever you needed to refresh.

 

[hkimpact]

Well after thinking about this I ended up writing something that performed a count.

Here was my code that I ended up writing.

TotalRecords: (IIf(IsNull([Field1]),0,[Field1]>0)+IIf(IsNull([Field2]),0,[Field2]>0)+IIf(IsNull([Field3]),0,[Field3]>0)+IIf(IsNull([Field4]),0,[Field4]>0)+IIf(IsNull([Field5]),0,[Field5]>0)+IIf(IsNull([Field6]),0,[Field6]>0)+IIf(IsNull([Field7]),0,[Field7]>0))*-1

This works the way I inteded it to. There probably is more ways of doing this too, but I wanted the short and simple way.

Thanks for the reply though plog. I appreciate it.

 

[plog]

That's neither simple, short nor a count.

 

[hkimpact]

Haha you are right. Definitely not simple but it worked . I appreciate your time helping out.