Expression - another Query Error

[jamescullis]

I've got this currently where I'm summing multiple orders for an project

ProductSubtotal: Round(CCur(DSum("[TotalOrderDetail]","OrderDetailsQuery","[OrderID] =" & [OrderID])),2)

my problem is if an [OrderID] doesn't exist, then the Nz function still won't work for me and returns #ERROR

I'm most likely placing the NZ in the wrong places, maybe not.

any help please?

 

[namliam]

You are probably using a dsum function where you shouldnt, Domain functions like Dlookup and DSum should only be used sparingly and in exceptional cases.

This isnt one of those cases is my guess instead you should make a left outer join with your orders and do a group by-sum query...
Also given your query name, I suggest you read this as well:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention

Just in case you feel I am wrong, your NZ should go around the Dsum
NZ(Dsum(...),0)

 

[jamescullis]

thank you for the feedback, points taken.

I'll research your response "make a left outer join with your orders and do a group by-sum query" as that is still beyond my comprehension.

 

[namliam]

You have a query, in query design you have your orders table there right?
Add your order details as well and "drag" your column OrderID from your order table to your Column OrderID in your Orderdetail table.
This creates an INNER JOIN between the two tables.
Now double click on the line and change the inner join (option 1) to an outer join (either left or right depending on if you choose option 1 or 2)
Read the description carefully and test both to see their effects.

See the wiki http://nl.wikipedia.org/wiki/Join_(SQL)
or this blog http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
for more details on INNER JOIN and OUTER JOIN details

 

[jamescullis]

Hi namliam, that worked a treat, thank you. after a few video's I'm off and running.

However, I'm still getting #Error where no values OrderDetails exist. I tried the Nz function without any luck. please see if the SQL below helps.

thank you


SELECT tblOrder.OrderID, Sum(tblOrderDetails.TotalOrderDetail) AS SumOfTotalOrderDetail, Sum(tblOrderDetails.TotalOrderFreight) AS SumOfTotalOrderFreight, Round(CCur([SumOfTotalOrderDetail]+[SumOfTotalOrderFreight]+[GST]),2) AS GrandTotal
FROM tblOrder LEFT JOIN tblOrderDetails ON tblOrder.OrderID = tblOrderDetails.OrderID
GROUP BY tblOrder.OrderID;

 

[jamescullis]

Hi namliam, irgore my comments, I switched to Option 3 on Join Properties and its returning the values I want.

thanks