iif statement in calculated field

[Frank72]

Hi,

i'm trying to get the sum of a field (amount) based on whether or not another field (late) is valued 1 or 2

i have tried:
DSum("[amount]","qryrebatereport","[late]=1&quot or
iif([late]=1,sum([amount1]),sum([amount2]))

i have tried creating 2 different fields in thwe query based on thye amount neither of the above i can get to work in query or text box on report

any ideas would be greatly appreciated

Frank

 

[nickjar2]

Hi Frank,

Maybe u could try grouping in your report (assuming u r using a report). Group on the Late field, and do a sum in the group footer. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[Frank72]

Hi nikjar

thanks for the reply, i've grouped the report on late but when i put just a normal sum on [amount] in the late footer and produces error for sum and rest of records

 

[nickjar2]

I have created a report based on my dummy table. I have grouped on the Late field and included a group footer. I have used an unbound text box and put this in the Control Source:

=Sum([Late])


This should work. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)