DLookUp isn't quite right

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to creat an Updateable Query.
Based on a GroupBy Query I have the following;
SELECT Orders.OrdersID, Orders.ClientID,
DLookUp("[Total]","Query1","[OrdersID]") AS [Amt Due], Orders.Paid
FROM Orders
WHERE (((Orders.Paid) Is Null));
but it shows the same total for all OrderID. How do I get it to show each
total for each OrderID.
Any help appreciated. Thank you inadvance
 
Hi John, there are two ways to correct this problem

Method 1: correct the criteria caluse in the DLookup
SELECT Orders.OrdersID, Orders.ClientID,
DLookUp("[Total]","Query1","[OrdersID]=" & Orders.OrdersID) AS [Amt Due],
Orders.Paid
FROM Orders
WHERE (((Orders.Paid) Is Null));

Method 2: include Query1 in this query
SELECT Orders.OrdersID, Orders.ClientID,
Query1.Total AS [Amt Due], Orders.Paid
FROM Orders INNER JOIN Query1 ON Orders.OrdersID = Query1.OrdersID
WHERE (((Orders.Paid) Is Null));

HTH, Graeme.
 
Back
Top