Try using NZ to compensate for any fields that do not have values, e.g. MyValue:Nz([YourFieldName])
I don't use Access as often as I'd like, so I'm not sure how I might implement this, but here's the pertinent code snippet:
Code:
strParams = fnConstructWhere(strParams)
strSQL = " TRANSFORM Count(tblcases.caseid) AS Cases " _
& " SELECT tblcases.casetype, Count(tblcases.caseid) AS [Total Cases]" _
& " FROM tblcases"
strSQL = strSQL & strParams & " GROUP BY tblcases.casetype PIVOT tblcases.status;"
Set MyQuery = MyDB.CreateQueryDef(strQName, strSQL)
DoCmd.OpenQuery strQName, acViewPreview, acReadOnly
Please Help?