DCount problem, count including blank

[Zc kaiva]

Hi all, i want to count all records inside AmBus column inside query qryBusCountName excluding blanks (" " )
But DCount is counting blank fields as well.

=Nz(DCount("[AmBus]","[qryBusCountName]"),0)

How can i exlcude blanks in my count?

 

[bastanu]

First of all why do you have blanks? And what do you consider a blank? What you're showing is a space (" "). A zero length string or ZLS ("") will be different and so is a Null.
To do it for what you show you should try =Nz(DCount("[AmBus]","[qryBusCountName]","[AmBus]<>'" & " " & "'"),0).
Cheers,

 

[Zc kaiva]

First of all why do you have blanks? And what do you consider a blank? What you're showing is a space (" "). A zero length string or ZLS ("") will be different and so is a Null.
To do it for what you show you should try =Nz(DCount("[AmBus]","[qryBusCountName]","[AmBus]<>'" & " " & "'"),0).
Cheers,

Thank you, this helped.

 

[Pat Hartman]

Blanks and Null are not the same thing. The expression as written will NOT count null values but it will count ZLS (ZeroLengthStrings). To not count ZLS, modify the where clause of the query to eliminate them from the domain.

Vlad types faster

 

[Zc kaiva]

Blanks and Null are not the same thing. The expression as written will NOT count null values but it will count ZLS (ZeroLengthStrings). To not count ZLS, modify the where clause of the query to eliminate them from the domain.

Vlad types faster

I changed "" to Null and now its working fine. Thank you.

 

[arnelgp]

changed "" to Null and now its working fine

that's what i thought. no matter how many spaces you put at the end of a field, access remove them.

 

[Galaxiom]

I changed "" to Null and now its working fine.

Exactly what expression did you use? Normally Null cannot be used with the equality operator but rather with IS.

 

[Pat Hartman]

If somefield & "" <> ""

Is a way to check for both null and ZLS at the same time. When you concatenate a null value to "", you get a ZLS so Vlad's solution should have worked. And as Galaxiom mentioned comparing a field that is null to null as in

If somefield = null

Will ALWAYS return null (i.e. NOT TRUE) so please post the expression you ended up with.

 

[Sun_Force]

try =Nz(DCount("[AmBus]","[qryBusCountName]","[AmBus]<>'" & " " & "'"),0).
Cheers,

Is it different with :
=Nz(DCount("[AmBus]","[qryBusCountName]","[AmBus]<>' '"),0)

thanks.

 

[Pat Hartman]

Looks like a space between the two single quotes. If so, remove it.

 

[Zc kaiva]

Exactly what expression did you use? Normally Null cannot be used with the equality operator but rather with IS.

I changed "" to null in expression below on my query.
AmBus: IIf([BusNo] like "L19*" And [AM/PM]="AM", [BusNo],Null)

And on the my report i used
=Nz(DCount("[AmBus]","[qryBusCountName]"),0)

 

[Sun_Force]

Looks like a space between the two single quotes. If so, remove it.

@bastanu has the same space between double quotes in post #2.

 

[Pat Hartman]

So, remove it. A space has a different ASCII value than a ZLS.