Count NULL togerther w/ “No Stat” records

jasminewhite

Registered User.
Local time
Yesterday, 19:18
Joined
Dec 8, 2011
Messages
26
My table:
CHAPTER FIELD1 FIELD2
1 NULL “No Stat”
1 “No Stat” NULL
1 NULL NULL
1 “No Stat” “No Stat”
1 otherVal otherVal
The result that I want:
CHAPTER COUNTfield1 COUNTfield2
1 4 4
I want to select records that contain either NULL or “No Stat” in either FIELD1 OR FIELD2
then Count #rec for any field that has NULL or “No Stat”
The problem is the count function does not count NULL, so I use IIf statement to replace NULL with “No Stat”. I can display those values on the screen correctly, but cannot count these NULL recs together with the “No Stat” recs. Please give me the SELECT statement that does this. Thank you so much.
 
In your Totals query
Code:
COUNTfield1: Count(IIF(IsNull([Field1]) Or [Field1] = "No Stat", 1, Null))
 
Thank you so much. I've been struggling with the count function on NULL value for days now.
 
field1 has both NULL and other text. However, your code only count the records with other text, the count for NULL records is 0. Please help, I've been looking at this count function for too long, come to a dead end.
 
The code I gave you caters for Null values, so I can only conclude that your field contains the zero-length string as well as Null:
Code:
COUNTfield1: Count(IIF([COLOR=Blue]Len([Field1] & "") = 0 [/COLOR]Or [Field1] = "No Stat", 1, Null))
However, if you're only looking to count only certain values, are you filtering your query by those values too?
 
My DB table field has the following data: NULL, 0-len text, "No Stat", "red", "green". My query counts these records & report the count in 2 columns: 1) NULL, 0-len txt or "No Stat" 2) "red" or "green. Thank you for any help that I can get. Another question, do I need SELECT DISTINCT or just SELECT
 
There was no indication if you used the code I posted in my last post or not.
 
yes, I did. I'm sorry, I forgot to mention counting for NULL works. But I also need to count for NULL along with other value such as "red", "green" as well.
 
yes, there is no filter (no where clause), just count each type of record
 
there is no WHERE clause for the SELECT statement, it pulls in all records. my countNULL column: count([field] & "".... counts NULL, my countRED column: count ([field]="Red"..... counts RED. so it finally works. Thanks for all your help, appreciate it. BTW, how do I close a thread as RESOLVED?
 
That functionality hasn't been implemented on this forum I'm afraid. Just imagine it as being marked as Resolved :)

Good luck with the rest of your project!
 

Users who are viewing this thread

Back
Top Bottom