CountIf alternative for form

[notsirk]

Greetings!

I have a form where I'm trying to display the count of records. Originally, the Control Source for the field that displays the count had the simple

=Count([fieldName])

code but apparently it is counting fields containing an empty string. This field may have either a null value or empty string (but I'd rather not go into that).

If tried both of these with no luck:

=Count(IIf([fieldName]>0, [fieldName], 0))

=Count(IIf(([fieldName]>0) And ([fieldName] Is Not Null), [fieldName], 0))

Any ideas?

 

[Isskint]

Use the aggregate function Dcount() to count fields meeting a specific criteria, and use the NZ() function to identify records to count.

DCount("Field","Table","NZ(FieldName,0)>0")

 

[notsirk]

This returns nothing. Is this the correct syntax for Control Source in a form? I usually see brackets instead of quotes.

 

[Isskint]

A form does not have a control source, controls do.
The quotes are used because the function DCount() requires data passed as strings. DCount() requires the following arguments:
Field name to count
Table name to look in
Criteria to reduce the records it counts
You are, I think, confusing this with Field Name square brackets [].

If you are using this to return a value in a textbox then use: =DCount("Field","Table","NZ(FieldName,0)>0")

 

[notsirk]

Yes, I misspoke in my original post and reply. I am editing the control source of a control within a form.

But still, your code returns nothing. Also, I shouldn't think it necessary to name the table as the data from said table is already populated into the form. Again, the current code works except that it's counting empty strings (at least that's what I think it is doing).

=Count([fieldName])

In addition, the form is filtering the data from the table so I want to count the filtered results.

 

[Isskint]

Count is a simple function, generally used to return the count of an object type (EG Forms in a DB, Fields in a table, records in a table/query etc). In your case so far it 'works' only because it is simply counting how many of fieldname is present in the forms recordset. When you want to count records meeting a certain criteria you use Dcount().

Can i check you have changed Field, Table and FieldName to the names of your table and fields? It would be handy if you could attach the DB (or those parts st issue)

 

[notsirk]

Okay, open frmCardMenu and you'll see at the bottom where my total counts are. I had to replace all of the records because they contain sensitive data so this is not giving the wrong counts (except where I put in your code).

Perhaps you may still find a better way...

 

[Isskint]

Ok, i have opened your db. It appears to be counting correctly. In tblCardUsers you have 29 Users, 19 UserGL134 and 19 UserGL3456 and these are the values being returned in the counts. If i change values the counts mirror this. What values are you seeing/Expecting?

 

[notsirk]

Well in the Db with the real data, I have over 4,000 total entries (most are suspended users). It counts the users fine but the userGL1234 and userGL3456 are off and the latter considerably higher than it should be. For 1234, I get a count of 1687 but it should be 1683; for 3456 I get 1382 but there are only 255!

I don't know for a fact that it is counting empty strings but that's my best guess as some users move around, some get suspended and reactivated, etc. so the field value changes.

 

[Isskint]

Could you copy tblCardUsers and delete everything except GL1234, GL3456, the Primary Key and - if possible User - (delete data OR delete fields)? Put that table in a new blank DB and attach that. I will then go through those 2 fields and search for the 'issue'. It may be blanks (as you suspect) or it may be spaces or something else. It just requires a bit of checking.

 

[notsirk]

Here you go. Thank you for spending so much time on this!