query error

[Guest]

hi,

I ahve the following:

SELECT DISTINCT tbl_resolved.resolvedStatus,
Count(tbl_resolved.resolvedStatus) AS CountOfstatus,
IIf(Forms!frm_criteria!status1 Is Null,"",[New Institution Name]) AS test,
IIf(Forms!frm_criteria!status2 Is Null,"",[Division Name]) AS
division_name_value FROM tbl_resolved WHERE tbl_resolved.[resolvedstatus] IN
('BK','CHARGEOFF','CURRENT_AMD/DFR','CURRENT_BKAMD/DFR','CURRENT_PAID','PAIDOFF','REPO',)
AND IIf(Forms!frm_criteria!status1 Is Null,"",[New Institution Name]) AS test
IN ('Arcadia','AutoOne','TranSouth',) AND IIf(Forms!frm_criteria!status2 Is
Null,"",[Division Name]) AS [division_name_value] IN
('Central','Closed','CTNA',) GROUP BY tbl_resolved.resolvedStatus ORDER BY
tbl_resolved.resolvedStatus;

but I am getting an error message that says:
syntax error (missing operator)...

can anyone tell what is wrong upon just looking at this?

thanks in advance,
geebee

 

[Guest]

The Is Null statement works in SQL for example:
Where [SomeField] Is Null
In the IIf statement, it does not. The IIf is a VBA function, so you have
to use VBA syntax:

I fixed the first one as an example:
IIf(IsNull(Forms!frm_criteria!status1)l,"",[New Institution Name])

 

[Douglas J. Steele]

In addition to Klatuu's suggestion, you seem to have extra commas at the end
of your IN lists.