Access Sql Filter Query By Date For Two Values

I have an expression that shows the total inventory before all orders in and out: (Query3 just combines the stock list with ingoing and outgoing orders, and works fine) SELECT Que

Solution 1:

Here's an answer from a similar question. Use the MAX function so that the subqueries will only return a single row:

SELECT query3.products.id,
       query3.productname,
       query3.standardcost,
       query3.onhand,
       (
                SELECT   MAX(Nz(SUM([OrderJoin.Quantity]),0))
                FROM     query3
                WHERE    query3.shippeddate>[Enter End Date]
                OR       query3.shippeddate IS NULL
                GROUP BY query3.products.id,
                         query3.productname,
                         query3.standardcost,
                         query3.onhand;) AS outgoing,
       (
                SELECT   MAX(Nz(SUM([Query1.Quantity]),0))
                FROM     query3
                WHERE    query3.datereceived>[Enter End Date]
                OR       query3.datereceived IS NULL
                GROUP BY query3.products.id,
                         query3.productname,
                         query3.standardcost,
                         query3.onhand;) AS incoming,     
        [OnHand]+[Outgoing]-[Incoming] AS onhandafter,    
        [StandardCost]*[OnHandAfter] AS totalcost 
FROM query3 
GROUP BY query3.products.id, query3.productname, query3.standardcost, query3.onhand;