DCount problem, count including blank (1 Viewer)

Zc kaiva

New member
Local time
Today, 21:46
Joined
May 9, 2021
Messages
9
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

AWF VIP
Local time
Today, 14:46
Joined
Apr 13, 2010
Messages
1,401
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

New member
Local time
Today, 21:46
Joined
May 9, 2021
Messages
9
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

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
43,006
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

New member
Local time
Today, 21:46
Joined
May 9, 2021
Messages
9
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:46
Joined
May 7, 2009
Messages
19,175
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
43,006
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.
 
Last edited:

Sun_Force

Active member
Local time
Tomorrow, 06:46
Joined
Aug 29, 2020
Messages
396
try =Nz(DCount("[AmBus]","[qryBusCountName]","[AmBus]<>'" & " " & "'"),0).
Cheers,

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

thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
43,006
Looks like a space between the two single quotes. If so, remove it.
 

Zc kaiva

New member
Local time
Today, 21:46
Joined
May 9, 2021
Messages
9
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)
 

Users who are viewing this thread

Top Bottom