Count only Not Null fields in report in 2007

[Rachael]

In Access 2007, I am trying to count a particular field only when the field
is not null, but no matter what I try (have reviewed instructions from
several other posts), it keeps returning a count of all records, regardless
of content.

is there some setting somewhere I might be missing? I've copied and pasted
several suggested solutions from other posts (changing the field name to
match my own, of course), and I still keep getting a total count.

Can you help?
Thanks.

 

[John Spencer]

I would guess (you didn't post the actual query or code) that the field is not
null. It is probably a zero-length string.

TRY the following expression. It handles nulls, zero-length strings, and
strings that consist of only space characters - and yes it is possible to have
strings that consist of only spaces if you are linking to an SQL server, or an
Excel worksheet, or ...

Abs(Sum(Len(Trim([TheField] & "") <> 0)))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Rachael]

When trying this, I'm getting a data type mismatch error. This is a text
field (something I failed to mention previously), and isn't the SUM function
designed for numbers?

I would guess (you didn't post the actual query or code) that the field is not
null. It is probably a zero-length string.

TRY the following expression. It handles nulls, zero-length strings, and
strings that consist of only space characters - and yes it is possible to have
strings that consist of only spaces if you are linking to an SQL server, or an
Excel worksheet, or ...

Abs(Sum(Len(Trim([TheField] & "") <> 0)))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

In Access 2007, I am trying to count a particular field only when the field
is not null, but no matter what I try (have reviewed instructions from
several other posts), it keeps returning a count of all records, regardless
of content.

is there some setting somewhere I might be missing? I've copied and pasted
several suggested solutions from other posts (changing the field name to
match my own, of course), and I still keep getting a total count.

Can you help?
Thanks.

 

[John Spencer]

First, sorry I got my parentheses wrong in the expression. It should have been
Abs(Sum(Len(Trim([TheField] & ""))<>0))

Second, you are correct that Sum totals numbers.
The reason the above expression should work is that
Trim(TheField & "") will force a zero-length string if the field has no
characters in it at all.
then LEN returns zero for a zero-length string and some number for all
other cases
Then the comparison <> 0 will return TRUE (-1) or False (0)
SUM adds up all the returned values and returns a negative number
ABS removes the negative sign.

IF you are more comfortable with using Count then the expression can be written as
Count(IIF(Len(Trim([TheField] & ""))<>0,1,Null))

Or if you are sure about the field being either null or a zero-length string
or a string with multiple characters.

Count(IIF([TheField]="" or TheField is Null,Null,1))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

When trying this, I'm getting a data type mismatch error. This is a text
field (something I failed to mention previously), and isn't the SUM function
designed for numbers?

I would guess (you didn't post the actual query or code) that the field is not
null. It is probably a zero-length string.

TRY the following expression. It handles nulls, zero-length strings, and
strings that consist of only space characters - and yes it is possible to have
strings that consist of only spaces if you are linking to an SQL server, or an
Excel worksheet, or ...

Abs(Sum(Len(Trim([TheField] & "") <> 0)))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

In Access 2007, I am trying to count a particular field only when the field
is not null, but no matter what I try (have reviewed instructions from
several other posts), it keeps returning a count of all records, regardless
of content.

is there some setting somewhere I might be missing? I've copied and pasted
several suggested solutions from other posts (changing the field name to
match my own, of course), and I still keep getting a total count.

Can you help?
Thanks.