dsum

[Ezekiël]

Hi everybody,

Who can help me with the dsum function in vba?I can't get it quit right if i
use a select statement in the expression.

I would like to make a calculation by using 2 tables that are related. One
table contains prices of orders and the other table is a list with values
which i would like to calculate.

The code looks like this:

Dsum("[price]","orders","Id=me![cmbOrderId]")*
Dsum("Select....","pricegroups","? (I don't know what to fill this part")

Greetings,

Ezekiël

 

[Tim Ferguson]

Dsum("[price]","orders","Id=me![cmbOrderId]")*

This one is unlikely to work, although I have to say that I have not tried
DSum like this. The SQL interpreter will not know what Me refers to. The
somewhat simpler method is:

DSum("Price", "Orders","Id=" & me!cmbOrderID.Value)

Dsum("Select....","pricegroups","? (I don't know what to fill this part")

If the SELECT was going to go anywhere, it would be in the third element,
not the first. The simplest way for you would be to set up a normal Query
object and make sure it is returning the right answers, and then using
DLookup or DSum on that:

If qryGroupOnOrders looks something like

SELECT OLines.OrderNumber,
SUM(Products.Price) AS OrderTotal
FROM OLines RIGHT OUTER JOIN Products
ON Olines.ProductCode = Products.CatalogNumber
GROUP BY OLines.OrderNumber
ORDER BY OLines.OrderNumber;


then you could use

DLookUp("OrderTotal", "qryGroupOnOrders", _
"OrderNumber=" & Me!cmbOrderID.Value )

Hope that helps


Tim F