SumProduct in Report

[JuniorWoodchuck24]

Trying to do the SumProduct in a Report. My question is how do you type it in VBA to work. I've got the following fields coming in XLift and Weight.

If I check the box to allow Excel worksheet functions I've come to this:

OverallWeight = Sum(Weight) 'Note I've got the total here already

txtFinalXposition = WorksheetFunction.SumProduct(arg1, arg2) / OverallWeight

I'm curious on how to enter the two fields [XLift] and [Weight] into arg1 and arg2.

Thanks

 

[PNGBill]

You sum on an Unbound text box control with the datasource property being =Sum([fieldname]) where the controls you are summing are Bound text Box Controls.

If they are already calculated Unbound controls then refer to their control name, not the original field name.
To add use =[control name or field name]+ or -[controlname or fieldname]

 

[JuniorWoodchuck24]

txtFinalXposition = WorksheetFunction.SumProduct(arg1, arg2) / OverallWeight

When I place [XLift] and [Weight] into arg1 and arg2, the result is equal to 0.

XLift is pulling from a table and Weight is pulling items off a table to the report to auto fill it. When multiplied and added together the overall shouldn't be equal to 0. I might be missing something that is causing the error of giving me a 0 value.

 

[PNGBill]

I assumed your data was already on the report as either a bound text box control or a calculated text box control.

If you are using data not on the report, and you can't easily add it - it doesn't need to show but just be in the record source, then you will need to use DLookup or another method to include the data.

You could try and get the data into an unbound text box control with DLookup("[fieldname]", "tablename")

Then do all your other calculations using this new unbound text box control.

When all is ok, just make the text box control invisible so it doesn't show in your report.

 

[JuniorWoodchuck24]

When you said DLookup you helped turn the light bulb on. I just set up a query with the fields I needed and ran a Product calc on it and then ran a DSum on the Product Field in the report. SumProduct function completed, thanks again.