need to count only records that are not null

[barlee]

I need to display in a report a count of the records where a certain field is not null. For example, I have a list of people that owe money, and I want to count all the records, then I want to count only those people that paid....

 

[Alc]

Where the field isn't null.

SELECT Count(*) FROM [I]tablename[/I]
WHERE Not IsNull([I]fieldname[/I]);

Where the field is null.

SELECT Count(*) FROM [I]tablename[/I]
WHERE IsNull([I]fieldname[/I]);

 

[barlee]

I am trying to show this count on a report, not in a query. Are you suggesting that I need to create a query first, and that I can't do this in the report design?

 

[barlee]

never mind, I figured it out! thanks for sending me the right direction! I added the count to my query, and was therefore able to use that on my report.

 

[LPurvis]

What direction was it that you took? (It's unclear from the end of this thread as it stands).

In a report (or form) you can count all rows in a similar way to a query.
In a textbox use the expression
=Count(*)

By default - the Count function counts only non-Null values.
The Count(*) expression circumvents this - by not specifying a field - it counts rows and is optimised to do so (you're unlikely to ever be able to detect this though

However if there is a specific field you want to count - then specifying that field will count only the non-Null entries.
=Count([FieldName])

I dare say that's the way you went?
But it's best to make it clear for future readers.

Cheers.

 

[barlee]

In my query I had grouped the columns by clicking the 'Totals' button in Design View becasue I was summing the payments made, so I changed the grouping on a new column that I called 'Countayment Amount' to Count, then added criteria "Is Not Null", as I have read in many places that the Count(*) function counts everything, not just not nulls....

in my report, I simply displayed the Count field in a text box.

 

[batyrenko]

What direction was it that you took? (It's unclear from the end of this thread as it stands).

In a report (or form) you can count all rows in a similar way to a query.
In a textbox use the expression
=Count(*)

By default - the Count function counts only non-Null values.
The Count(*) expression circumvents this - by not specifying a field - it counts rows and is optimised to do so (you're unlikely to ever be able to detect this though

However if there is a specific field you want to count - then specifying that field will count only the non-Null entries.
=Count([FieldName])

I dare say that's the way you went?
But it's best to make it clear for future readers.

Cheers.

Thank you so much!

 

[theDBguy]

Thank you so much!

Hi. This is an old thread; but, welcome to AWF!