handling null parameters from controls (again...)

[gbshahaq]

i've looked on previous postings about this problem and none of the proposed solutions seem to work for me.
in particular I have tried those suggested here 59853 and here fontstuff.com

can someone advise me on where i'm going wrong pls?

PARAMETERS [Forms]![FRM_Report_Chooser]![ComboTeam] Text ( 255 );
SELECT Advisers.Team, Advisers.Adviser, DATES.Month, q_tbl_03_ALL_CALLS.[SumOfCalls served] AS [ALL calls served], q_tbl_01_CSDEMAIL.[SumOfPromo email addresses captured in CSD], q_tbl_02_EMACSEMAIL.[SumOfPromo email addresses captured in EMACS], [q_tbl_01_CSDEMAIL]![SumOfPromo email addresses captured in CSD]+[q_tbl_02_EMACSEMAIL]![SumOfPromo email addresses captured in EMACS] AS [E-Mail_Total], [E-Mail_Total]/[SumOfCalls served] AS [E-mail %], q_POT_EMAIL_SUM.[Total Of POT_Email], [Total Of POT_Email]/[SumOfCalls served] AS [E-mail_Tgt_%], [E-Mail_Total]/[Total Of POT_Email] AS [%_e-mail_tgt_achieved]
FROM Month_Picker INNER JOIN (q_POT_EMAIL_SUM INNER JOIN (q_tbl_03_ALL_CALLS INNER JOIN (q_tbl_02_EMACSEMAIL INNER JOIN ((q_tbl_01_CSDEMAIL INNER JOIN Advisers ON q_tbl_01_CSDEMAIL.Adviser = Advisers.Adviser) INNER JOIN DATES ON q_tbl_01_CSDEMAIL.Month = DATES.Month) ON (q_tbl_02_EMACSEMAIL.Month = DATES.Month) AND (q_tbl_02_EMACSEMAIL.Adviser = Advisers.Adviser)) ON (q_tbl_03_ALL_CALLS.Month = DATES.Month) AND (q_tbl_03_ALL_CALLS.Adviser = Advisers.Adviser)) ON (q_POT_EMAIL_SUM.Month = DATES.Month) AND (q_POT_EMAIL_SUM.Adviser = Advisers.Adviser)) ON Month_Picker.Month_Picker = DATES.Month
GROUP BY Advisers.Team, Advisers.Adviser, DATES.Month, q_tbl_03_ALL_CALLS.[SumOfCalls served], q_tbl_01_CSDEMAIL.[SumOfPromo email addresses captured in CSD], q_tbl_02_EMACSEMAIL.[SumOfPromo email addresses captured in EMACS], [q_tbl_01_CSDEMAIL]![SumOfPromo email addresses captured in CSD]+[q_tbl_02_EMACSEMAIL]![SumOfPromo email addresses captured in EMACS], q_POT_EMAIL_SUM.[Total Of POT_Email], DATES.MONTH_NUMBER, IIf(IsNull([Forms]![FRM_Report_Chooser]![ComboTeam]),True,[Advisers]![Team]=[Forms]![FRM_Report_Chooser]![ComboTeam])
HAVING (((IIf(IsNull([Forms]![FRM_Report_Chooser]![ComboTeam]),True,[Advisers]![Team]=[Forms]![FRM_Report_Chooser]![ComboTeam]))<>False))
ORDER BY Advisers.Team, Advisers.Adviser, DATES.MONTH_NUMBER;

 

[Mile-O]

Use the Nz() function around the form references.

ie.

Nz([Forms]![MyForm]![MyControl])

 

[gbshahaq]

i don't geddit - but i tried it. no change.

IIf(IsNull(Nz(Forms!FRM_Report_Chooser!ComboTeam)),True,Advisers!Team=Nz(Forms!FRM_Report_Chooser!ComboTeam))
HAVING (((IIf(IsNull(Nz(Forms!FRM_Report_Chooser!ComboTeam)),True,Advisers!Team=Nz(Forms!FRM_Report_Chooser!ComboTeam)))<>False))

after amending, Access automatically removed the square brackets....
any other ideas?

Use the Nz() function around the form references.

ie.

Nz([Forms]![MyForm]![MyControl])

 

[Jon K]

In a Totals Query, Access defaults to "Group By" and filter the Groups with the Criteria. To tell Access to filter the records, we need to use the keyword "Where". See the query in the attached database.