Null values when calling query that calls a query

[pbassett]

My Excel worksheet needs to report incident counts by location over a specified time period (each month for 12 months). There may be multiple records per incident ID, so I use 2 queries: first I group by Incident ID to get distinct records, and then I sum by each location.
This works in Access but I get Null values for each location from VBA. Apparently calling a query that uses Sum that calls a query that uses Group By causes issues. Or maybe it's because of the liberal use of In() and Not In().

This is the 1st query - it retrieves distinct records:
SELECT INCIDENT_ID, DATE_OF_INCIDENT, LOCATION_FACILITY, LOCATION_UNIT, EMERGENCY_CODE
FROM H74ASHIR_VIEW_INCIDENT_INFO_BY_PATID
GROUP BY INCIDENT_ID, DATE_OF_INCIDENT, LOCATION_FACILITY, LOCATION_UNIT, EMERGENCY_CODE
HAVING (((EMERGENCY_CODE) Like "*Code Gray*"));

This is the 2nd query (that calls the 1st query) - it gets the sum of incidents by location. There are *many* locations, so for brevity I only included the 1st location:
SELECT Sum(IIf([location_facility]="Civil Campus" And [location_unit] Like "*ironwood*",1,0)) AS Civil_IW
FROM qryPreCodeGray
WHERE (((DATE_OF_INCIDENT) Between [Enter Start Date:] And [Enter End Date:]) AND ((EMERGENCY_CODE) Like "*Code Gray*"));

FYI, the VBA code -
For i = firstMonthColumn To lastMonthColumn
Application.StatusBar = "Code Gray: Processing Month " & Cells(2, i) & " ..."
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "qryCodeGray"
param1Range = Cells(2, i)
.Parameters.Append .CreateParameter("param1", adVarWChar, adParamInput, adChar, param1Range) ' e.g. 3/1/2016
.Parameters.Append .CreateParameter("param2", adVarWChar, adParamInput, adChar, Month(param1Range) & "/" & _
DateSerial(Year(param1Range), Month(param1Range) + 1, 1) - _
DateSerial(Year(param1Range), Month(param1Range), 1) & "/" & _
Year(param1Range)) ' e.g. 3/31/2016
Set rs = .Execute
If Not (rs.BOF And rs.EOF) Then
For j = 0 To rs.Fields.Count - 1
LArray = Split(rs.Fields(j).Name, "_") ' Field names are e.g. "Civil_IW"
campus = LArray(0)
location = LArray(1)
campusRow = Application.WorksheetFunction.Match(campus, Range("A:A"), 0)
locationRow = Application.WorksheetFunction.Match(location, Range("A" & campusRow & ":A" & ActiveSheet.Rows.Count), 0) + campusRow - 1
Cells(locationRow, i).Value = IIf(rs.Fields(j) <> "Null", rs.Fields(j), 0)
Next j
End If
.Parameters.Delete "param1"
.Parameters.Delete "param2"
Next i

 

[pbassett]

I fixed it by replacing wildcards with InStr, i.e.

from
SELECT Sum(IIf([location_facility]="Civil Campus" And [location_unit] Like "*ironwood*",1,0)) AS Civil_IW
to
SELECT Sum(IIf([location_facility]="Civil Campus" And InStr([location_unit], "ironwood")<>0,1,0)) AS Civil_IW

Go figure ...