Problem using Sum() with a calculated control

[jrollins]

I have a multiple-record report with a calculated control containing the following expression:

=DLookUp("[RT1 TOTAL]","SELECT2 RT1")+DLookUp("[RT2 TOTAL]","SELECT2 RT2")+DLookUp("[RT3 TOTAL]","SELECT2 RT3")+DLookUp("[RT4 TOTAL]","SELECT2 RT4")+DLookUp("[RT5 TOTAL]","SELECT2 RT5")+DLookUp("[RT6 TOTAL]","SELECT2 RT6")+DLookUp("[RT7 TOTAL]","SELECT2 RT7")+DLookUp("[RT8 TOTAL]","SELECT2 RT8")+DLookUp("[RT9 TOTAL]","SELECT2 RT9")+DLookUp("[RT10 TOTAL]","SELECT2 RT10")+DLookUp("[RT11 TOTAL]","SELECT2 RT11")+DLookUp("[RT12 TOTAL]","SELECT2 RT12")+DLookUp("[RT13 TOTAL]","SELECT2 RT13")

The control itself works properly, returning the correct value for each record in the report. In the form footer, I have a text box to display the total of all the values in this control. I tried to use =Sum([ControlName]) but it doesn't display anything in the text box. Any ideas?

 

[dhookom]

I hope you don't have too many records since this would be a fairly slow running report. As you have found, you can't sum a control. You can sum the expression from the control's record source. For instance
Detail Section control
Control Source: =Qty*Price
Footer Section control
Control Source: =Sum(Qty * Price)

BTW: Your expression suggests a very un-normalized table structure. I trust you either know what you are doing or it is beyond your control.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jrollins]

The table structure is beyond my control.

I tried this but it didn't work either:

=Sum(DLookUp("[RT1 TOTAL]","SELECT2 RT1")+DLookUp("[RT2 TOTAL]","SELECT2 RT2")+DLookUp("[RT3 TOTAL]","SELECT2 RT3")+DLookUp("[RT4 TOTAL]","SELECT2 RT4")+DLookUp("[RT5 TOTAL]","SELECT2 RT5")+DLookUp("[RT6 TOTAL]","SELECT2 RT6")+DLookUp("[RT7 TOTAL]","SELECT2 RT7")+DLookUp("[RT8 TOTAL]","SELECT2 RT8")+DLookUp("[RT9 TOTAL]","SELECT2 RT9")+DLookUp("[RT10 TOTAL]","SELECT2 RT10")+DLookUp("[RT11 TOTAL]","SELECT2 RT11")+DLookUp("[RT12 TOTAL]","SELECT2 RT12")+DLookUp("[RT13 TOTAL]","SELECT2 RT13"))