Control Source unbound Text Box Error 2

[cneill]

This is driving me mad

Checked all the nameing rules, so no problem as far as I can see

I have a unbound text box call SumDel in the Forms Header
in the Control Source I have
=iif([SchemeRef]=52,Sum([Delivered]))
SchemeRef and Delivered are bound text boxes in the forms Detail section being fed from the Forms query.
This results in an #Error
I have also tried
=Sum(iif([SchemeRef]=52,[Delivered]))
=Sum(NZ(IIf([SchemeID]=52,[Delivered])))
Again same result #Error

I have also tried
=[Delivered]
=[SchemeRef]
These work they return the value in the first row
Does any one have any further ideas?
Thanks CNEILL

 

[cneill]

Hi MajP

Now got this working, thanks you so much, just one question, I can't get the formating in the ListBox right for the TotalDelivered it shows e.g. 5000 but would like it to show 5,000, how do I get it to do this?

TheAceMan1

I tried your Idea and I still get #Error

Thanks everyone for you help

 

[MajP]

In your sql
format(Sum(Delivered),"#,###") AS TotalDelivered

 

[cneill]

Hi MajP,

Fantastic Thanks