DSum with Inner Join

[terbs]

I have created a Query which returns the correct results. But I need to assign the value of this query (its only a single field) to the value of a textbox on a click event. The query has a sum, which has criteria based on a related table, one of the criteria is dynamic.

I have searched around and apparently you cant use RunSql "Select" which is a shame, is there any other way to achieve the results im looking for??

Heres the query:

SELECT Sum(TBLSalesOrderItem.Qty) AS SumOfQty
FROM TBLSalesOrder INNER JOIN TBLSalesOrderItem ON TBLSalesOrder.ORDERID = TBLSalesOrderItem.ION
WHERE (((TBLSalesOrder.STATUSSHIP)=1 Or (TBLSalesOrder.STATUSSHIP)=2))
GROUP BY TBLSalesOrder.STATE
HAVING (((TBLSalesOrder.STATE)="NSWSales") AND ((Var(TBLSalesOrderItem.PartsID))=" & [I][COLOR="SeaGreen"]PartID[/COLOR][/I] & "));

I have tried this;

myNSWQty = DSum([QTY], "TBLSalesOrderItem", "INNER JOIN TBLSalesOrderItem ON TBLSalesOrder.ORDERID = TBLSalesOrderItem.ION WHERE (((TBLSalesOrder.STATUSSHIP) = 1 Or (TBLSalesOrder.STATUSSHIP) = 2)) GROUP BY TBLSalesOrder.STATE, TBLSalesOrderItem.PartsID HAVING (((TBLSalesOrder.STATE)='NSWSales') AND ((TBLSalesOrderItem.PartsID)=266))")

 

[KeithG]

Use the Dlookup function to look up the Sum from your query.