Count query

[liftshaft]

Can I make a count default to zero if criteria is not met, rather than passing a null value to a report?


<snip>

This was originally posted in Report forum and has been followed up on a fair bit if you would like background information please follow this link

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=46066

</snip>

Thank you

Chris Dalton

 

[pdx_man]

Check out the Nz() function in Access Help. This will work for you.


Nz({field or expression},0)

 

[liftshaft]

I hate to sound thick, but where would I put this?

 

[pdx_man]

In a Text Box control on a report, you can have the Control Source be:

= Nz(DCount("Anaesthesia Lip","Patient Stats","Date Between #" & [Start Date] & "# And #" & [End Date]),0)

Or if CountOfAnaesthesia Lip is a Field on the report, have:

= Nz([CountOfAnaesthesia Lip],0)

I would like to caution your use of the field name Date as it is a reserved word in Access for returning the system date. Try to qualify your fields with perhaps Pat_In_Date or something. Also, try not to use spaces in object and control names. If you forget to bracket them, Access may not parse the field name as you wanted it to.

HTH

Let me know if you need further assistance.

 

[liftshaft]

Figured it out....

Using group by in my query where I did not need to....

Thanks for all your help....

Please close thread....