Counting records that are not null

[TomC]

I am trying to count the number of records that are not null in a specific
field. My field is a 4-digit text field. I know the proper syntax for
counting records if the field is null, i.e., =Sum(IIf(IsNull([field]),1,0)).
My problem is that I don't know the syntax for counting fields using the
syntax "Is Not Null". I tried reversing the true/false counts as follows for
the "IsNull", but I get the same count for all nulls regardless of whether I
place the 1 in the true or false position which confuses me. For example, I
tried:
=Sum(IIf(IsNull([field]),0,1)) and I get the same total as the syntax above.
Any help would be greatly appreciated!!!!
Tomc

 

[Rick Brandt]

I am trying to count the number of records that are not null in a specific
field. My field is a 4-digit text field. I know the proper syntax for
counting records if the field is null, i.e., =Sum(IIf(IsNull([field]),1,0)).
My problem is that I don't know the syntax for counting fields using the
syntax "Is Not Null". I tried reversing the true/false counts as follows for
the "IsNull", but I get the same count for all nulls regardless of whether I
place the 1 in the true or false position which confuses me. For example, I
tried:
=Sum(IIf(IsNull([field]),0,1)) and I get the same total as the syntax above.
Any help would be greatly appreciated!!!!
Tomc

In query syntax you don't need the VBA IsNull() function. Try...

=Sum(IIf([field] Is Not Null,1,0))

 

[Marshall Barton]

I am trying to count the number of records that are not null in a specific
field. My field is a 4-digit text field. I know the proper syntax for
counting records if the field is null, i.e., =Sum(IIf(IsNull([field]),1,0)).
My problem is that I don't know the syntax for counting fields using the
syntax "Is Not Null". I tried reversing the true/false counts as follows for
the "IsNull", but I get the same count for all nulls regardless of whether I
place the 1 in the true or false position which confuses me. For example, I
tried:
=Sum(IIf(IsNull([field]),0,1)) and I get the same total as the syntax above.

Since all the aggregate functions ignore Null values, you
don't need any of that. Just use:

=Count([field])

 

[TomC]

Thanks for your help Rick. That was the answer I needed.

I am trying to count the number of records that are not null in a specific
field. My field is a 4-digit text field. I know the proper syntax for
counting records if the field is null, i.e., =Sum(IIf(IsNull([field]),1,0)).
My problem is that I don't know the syntax for counting fields using the
syntax "Is Not Null". I tried reversing the true/false counts as follows for
the "IsNull", but I get the same count for all nulls regardless of whether I
place the 1 in the true or false position which confuses me. For example, I
tried:
=Sum(IIf(IsNull([field]),0,1)) and I get the same total as the syntax above.
Any help would be greatly appreciated!!!!
Tomc

In query syntax you don't need the VBA IsNull() function. Try...

=Sum(IIf([field] Is Not Null,1,0))

 

[TomC]

Thank you Marsh. What a terrific service you all provide to us novices.
Saves us countless hours in the trial and error mode.
Tomc

I am trying to count the number of records that are not null in a specific
field. My field is a 4-digit text field. I know the proper syntax for
counting records if the field is null, i.e., =Sum(IIf(IsNull([field]),1,0)).
My problem is that I don't know the syntax for counting fields using the
syntax "Is Not Null". I tried reversing the true/false counts as follows for
the "IsNull", but I get the same count for all nulls regardless of whether I
place the 1 in the true or false position which confuses me. For example, I
tried:
=Sum(IIf(IsNull([field]),0,1)) and I get the same total as the syntax above.

Since all the aggregate functions ignore Null values, you
don't need any of that. Just use:

=Count([field])