Sumproduct Function

[PJFry]

Is there a Sumproduct function in Access?

To mimic this functionality I current create a recordset and loop through
adding the product of each line, something like this:

Dim cItem as Double
Dim aItem as Double
Dim sSQL as String

sSQL = "SELECT * FROM tList WHERE lngStore = 1"

rs.Open sSQL, cn

rs.MoveFirst

Do Until rs.EOF

cItem = rs!curCost * rs!lngPurchased * rs!dblTaxRt

aItem = aItem + cItem

rs.MoveNext

Loop

txtPurAmt = aItem

rs.Close
set rs = Nothing
aItem = 0
cItem = 0

The above works just fine, but as the database grows, I am afraid that all
this looping is going to hang me up somewhere. There are about 20 fields
that need to be populated.

I have tried to force a DSUM function to do this, but no luck.

Any suggestions?

I am running 2007, XP Pro SP2

Thanks!
PJ

 

[Marshall Barton]

Is there a Sumproduct function in Access?

To mimic this functionality I current create a recordset and loop through
adding the product of each line, something like this:

Dim cItem as Double
Dim aItem as Double
Dim sSQL as String

sSQL = "SELECT * FROM tList WHERE lngStore = 1"

rs.Open sSQL, cn

rs.MoveFirst

Do Until rs.EOF

cItem = rs!curCost * rs!lngPurchased * rs!dblTaxRt

aItem = aItem + cItem

rs.MoveNext

Loop

txtPurAmt = aItem

rs.Close
set rs = Nothing
aItem = 0
cItem = 0

The above works just fine, but as the database grows, I am afraid that all
this looping is going to hang me up somewhere. There are about 20 fields
that need to be populated.

Why doesn't Sum(curCost * lngPurchased * dblTaxRt) do what
you want?

 

[PJFry]

The sum didn't work because I can't use a sum function on a domain aggregate.
But your answer got me wondering if I could do a DSum on an expression,
rather an a single field, and you can!

txtPurAmt = DSum("curCost*lngQuantity*dblTax","tList","lngStore =1")

Thanks for getting me pointed in the right direction!

PJ