Yes/No Data type and the Null value in queries.

[JustAnElf]

Greetings one and all,



This is driving me NUTS. All I'm trying to do is count how many times the Yes/No field has been set to Yes in all of the records. My query works great until all of the records have the field set to NULL. At that point the query returns no value. How do I make it return a 0? I've been trying to use the Nz function with little success. For help I've included a picture of what my query looks like. The field ScriptIsComplete is the Yes/No field. Does anyone have any suggestions?

[Ruegen]

Can you show what tables you have involved?



You only require one field to count the field

scriptiscomplete
tblKnowledgeScripts
Count
true

for any null use

nz()

so nz(fieldthatisnull,0)

[June7]

Try:

SELECT Sum(IIf(ScriptIsComplete=Yes,1,0)) As CountYes FROM tablename;