Subtotal formula in Subform (1 Viewer)

Hi,

I have an invoice form in which there are 3 categories of works - time based, task based and other.

For task based, there is a Dlookup formula that looks up the program used and applies a rate to the work. The problem I am having is subtotalling the tasks in the forms footer, so I can see a running total in the main form.

Below is the formula i first tried


That just result was blank.

So then I tried I tried this


All I'm getting is a zero result.
Note CategoryID #1 is Time based; #2 is task; and #3 is Other

Any help in fixing this formula would be greatly appreciated.

in the subform footer, add a field with the formula:
=Sum(field)

Hi. Try looking up the rate first before multiplying it with the quantity. Right now, your formula is multiplying the quantity with the ID, so it’s not resulting in finding a rate.

Thank you. That worked. Changed the formula to the below and now everything is summing correctly


Thank you very much

Tophan said:

Thank you. That worked. Changed the formula to the below and now everything is summing correctly

Code:Copy to clipboard

=Sum(DLookUp("[Rate]","tblPrograms","PrgmID=" & [PrgmID])*[Qty])

Thank you very much

Click to expand...

Congratulations! Glad to hear you got it to work. Good luck with your project.