Sum(IIF(<expr.)) Problem in ADP Report Control

[Dave S]

I am currently in the process of converting an MDB to ADP linked to a
SQL Server back end. In the MDB I have expressions in both queries
and report controls that are essentially:

Sum(IIF(<expr>,1,0))

These give me counts for certain parameters.

In the queries I was able to create views were I replaced these with
CASE ... WHEN statements with a sum in the Group By clause.

However I can not find a way to replicate this functionality in a
report control, the Control Source will not even accept a CASE ...WHEN
statement and with IIF I receive the following error:

The expression 'Sum(IIf(<expr>,1,0)) is invalid

Any help you can offer would be greatly appreciated.

[Rich P]

Hi Dave,

Try creating your query in a stored procedure. Test out your SP in
query analyzer. If the SP works in QA, then set the Report recordsource
to the Stored Procedure. Note: If you have to pass a parameter to the
SP, you have to open the Report in Design View, set the params as
follows:

DoCmd.OpenReport reportName, acViewDesign

Reports("YourReport).inputParameters = "@UserId='" & strUserID & "',
@BeginDate='" & strBegingDate & "', @EndDate='" & strEndDate & "'"

DoCmd.Close acReport, reportName, acSaveYes
DoCmd.OpenReport reportName, acViewPreview

Rich

*** Sent via Developersdex http://www.developersdex.com ***