I have a query that works great for me except that it returns a zero when record is null just for MSR column. How can I make it so that the all null values return "---" in that field and all the other fields in the query. If that can't be done, I can settle for just a blank field. I added this to my first query but then no records were return after the change.
TRANSFORM Nz(Sum(tblAgentSummary.F4), "---") AS SumOfF4
I have 2 queries, one uses the other to generate the records. Not sure if I need to make a change on both for this to work. I have include both SQL statements from the query.
TRANSFORM Sum(tblAgentSummary.F4) AS SumOfF4
SELECT tblAgents.[Agent Name], tblAgents.Position
FROM tblAgents INNER JOIN tblAgentSummary ON tblAgents.[AgentID#] = tblAgentSummary.F3
WHERE (((tblAgentSummary.callDate) Between [forms]![frmAgentSummaryExport]![txtBusinessDate] And [forms]![frmAgentSummaryExport]![txtEndDate]) AND ((tblAgents.Position)=[PosParam1] Or (tblAgents.Position)=[PosParam2]) AND ((tblAgents.Department)=[forms]![frmAgentSummaryExport]![cboDepartment]))
GROUP BY tblAgents.[Agent Name], tblAgents.Position
ORDER BY tblAgents.Position
PIVOT tblAgentSummary.F1 In ("Salo","Other","GiftCard","Lend","Mort","Web");
SELECT qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Position, qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web