Trouble writing an IIF statement

[aageorge]

I have the following control source for a textbox in my report footer:

Sum(IIf([Shift]='Aft',[Expr1],0))

It very simply sums up Expr1 for Aft shifts. The problem is a few lines don't run Aft shifts and the text box returns a value of 'Num#'. How do you I write an expression that returns '-' if Aft shift does not exist for that line?

 

[cghoga]

in your detail section include a text box (text1) that determines whether the [Shift] field = Aft and if so it sets the value = [Expr1] otherwise to 0.

=iif([Shift]="Aft",[Expr1],0)

set the running sum of text1 to Over Group (maybe Over All - you may need to play with this).

set the visible property to No.

then in your footer, insert another textbox and set its control source to text1.

=[Text1]

it should give you the sum of the Expr1 where Shift = Aft.

hope this helps you.