We help IT Professionals succeed at work.

Access 2033 counting number of records in a form that the field is not null

Pdeters
Pdeters asked
on
I have an Access form that I am counting the number of records in a form. If the field is null I do not want to count it but still show the record for all the other fields.

This is what I am useing
Count([task1])

This field could be null but the other fields need to be showing. How do I count haev it count jsut the records where this field is not null
Comment
Watch Question

Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
What you have should work.

=Count(*) should give a count of all records

=Count([task1]) should give a count of records where [task1] is not null

Are you sure that you don't have records where [task1] is a zero-length string which looks like Null but is not?

Best regards,
Graham
Eric ShermanAccountant/Developer
CERTIFIED EXPERT

Commented:
Also try ....

DCount("[task1]", "YourTable/Query", "Nz([task1], '')<>''")


ET


Commented:
This will give you all the fields plus the count of all non-null values of fld3:

SELECT a.fld1, a.fld3, a.fld3, (SELECT Count(b.fld3) FROM myTable b) AS CountFld3 FROM myTable a
Commented:
That will give you the count value on every record returned by the query.  If you would like the count at the bottom of the recordset you can use a UNION query.  

SELECT a.fld1, a.fld3, a.fld3 FROM myTable a
UNION ALL
SELECT "Count","",Count(b.fld3) FROM myTable b

Commented:
Thanks, glad to help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.