Using IIF in a Stored Procedure linked to a Report

[Elen]

Hello,

Need help with the following problem, please.



Access Report (linked to a Stored Procedure) has the following calculated
field [text10] in the Detail section:



=IIF(IS NULL(DATE_PURCHASE),NULL, (COMMISSIONS/100)*COST_PRUDUCT)



but



=Sum(IIF(IS NULL(DATE_PURCHASE),NULL, (COMMISSIONS/100)*COST_PRUDUCT))

or

=Sum([text10])



doesn't work simply because as I said, the Report is linked to a Stored
Procedures.



Access help pages direct me to calculate my Report field in the Query (in
this case Stored Procedures). So the bottom line is I need to use the IIF
statement in a Stored Procedure.



I understand that such statement cannot be used but in its place to use
CASE. However, the Stored Procedure is generated using INNER JOIN of several
tables and the CASE syntax doesn't work. Works only if one table is used.



Therefore, I need to use the following equation in a Stored Procedure,
HOWTO?



=IIF(IS NULL(DATE_PURCHASE),NULL, (COMMISSIONS/100)*COST_PRUDUCT)



TIA,

Elen

 

[Adam Machanic]

I understand that such statement cannot be used but in its place to use
CASE. However, the Stored Procedure is generated using INNER JOIN of several
tables and the CASE syntax doesn't work. Works only if one table is used.

Can you post the full query with your CASE syntax that's not working?
Probably just a matter of a typo or something.