SUM a field in the footer (1 Viewer)

[jd_boss_hogg]

Hi All !..... hope some kind sole can help...

I have a field on a report with this as its control source...

-------------------
=(Format(Int([quantity]*(DLookUp("[Plot_times]","[ProductList]","

 = Reports![Mask_order_book]![CUST_REF]"))/60),"0") & ":" & Format([quantity]*(DLookUp("[Plot_times]","[ProductList]","[Code] = Reports![Mask_order_book]![CUST_REF]")) Mod 60,"00"))
----------------------

It turns a number like this "120" into elapsed time in hours and minutes like this "2:00"

I have a report with an elapsed time for all of the items. I simply want to SUM it at the report footer, but i guess my control source turns it from a number into text, which then cannot be summed ?

Most of the code above was gleemed from google searches, so please dont take me for some brainbox ...  :)

 

[pr2-eugin]

Why use DLookup? It makes the code so slow and inefficient. Try JOINING the two tables involved. If you need Sum, then Sum the value as you normally do then use the same method to split it into hours and minutes.

 

[jd_boss_hogg]

Do you mean take all the formatting out of the individual lines in the report so it goes back to "120" "60" etc, and then just format the SUM at the bottom?

i did contemplate this, but id rather have the formatted lines against each record in the report than the SUM in the footer. The SUM would just be a nice touch

thxs

 

[pr2-eugin]

No what I meant was,

= Int(Sum(yourDlookupStuff)/60) & ":" & Int(Sum(yourDLookupStuff) Mod 60)

 

[jd_boss_hogg]

Paul - cant get that to work in either the group footer or the report footer.

if i strip it down the the simplest...

=Sum((DLookUp("[Plot_times]","[ProductList]","

 = Reports![Mask_order_book]![CUST_REF]")))
[/QUOTE]

... then i get nothing displayed. If i take out the word SUM, then i get the last records value displayed.