CODEWITHSUNDEEP
ms-access
ddelpinal
ddelpinal

Reputation: 3

MS Access Query Expression in WHERE Clause

I'm having trouble passing in a query expression into a WHERE clause. Access does not pick up the new column (expression) name and prompts me to entire a value for it. The simplified query is below.

You'll notice the second portion of the WHERE clause includes the [Offset] column name, which is what is causing me problems. The result should check if the DateAdd/Offset value (DATE) is before the one present in another table. Any ideas on how to handle this? Thank you!

  SELECT po.Item, 
  ist.SKU, 
  po.[EX-FACTORY DATE], 
  Min(lt.Intransit_LT) AS LT, 
  DateAdd('d',[LT],po.[EX-FACTORY DATE]) AS Offset

  FROM (((tbl_Item_PO AS po 

  LEFT JOIN tbl_ItemSKUType AS ist ON po.item = ist.Item) 
  LEFT JOIN Supplier_and_LT AS lt ON po.Supplier = lt.Supplier

  WHERE (((ist.SKU) Is Not Null) 
  AND ([Offset]<(select top 1 AOD from tbl_Date)))

  GROUP BY po.Item, ist.SKU, po.[EX-FACTORY DATE], po.[QTY PER SHIPMENT], po.WHSE, wh.Region, po.Ship_Method;

Edit: I have tried to put in the expression explicitly but I am then prompted for a value for [LT]. Seems like Access doesn't recognize column names other than those from the source tables

Edit: HAVING is now giving me a datatype mismatch in teh query expression error. HEre's what I'm working with:

 SELECT po.Item, 
 ist.SKU, 
 po.[EX-FACTORY DATE], 
 Min(lt.Intransit_LT) AS LT, 
 po.Ship_Method
 FROM (((tbl_Item_PO AS po 
 LEFT JOIN tbl_ItemSKUType AS ist ON po.item = ist.Item) 
 LEFT JOIN Supplier_and_LT AS lt ON po.Supplier = lt.Supplier
 WHERE (((ist.SKU) Is Not Null) 
 GROUP BY po.Item, ist.SKU, po.[EX-FACTORY DATE], po.[QTY PER SHIPMENT], po.WHSE, wh.Region, po.Ship_Method
 HAVING (DateAdd('d',MIN(lt.[Intransit_LT]),po.[EX-FACTORY DATE]))<(select top 1 AOD from tbl_Date)

Upvotes: 0

Views: 1299

Answers (2)

Erik A
Erik A

Reputation: 32632

Your Offset includes LT, and LT contains a Min aggregate expression. Aggregate expressions cannot be used in the WHERE clause, only in the HAVING clause.

Try the following:

  SELECT po.Item, 
  ist.SKU, 
  po.[EX-FACTORY DATE], 
  Min(lt.Intransit_LT) AS LT, 
  DateAdd('d',[LT],po.[EX-FACTORY DATE]) AS Offset

  FROM (tbl_Item_PO AS po 

  LEFT JOIN tbl_ItemSKUType AS ist ON po.item = ist.Item) 
  LEFT JOIN Supplier_and_LT AS lt ON po.Supplier = lt.Supplier

  WHERE (ist.SKU Is Not Null) 

  GROUP BY po.Item, ist.SKU, po.[EX-FACTORY DATE], po.[QTY PER SHIPMENT], po.WHSE, wh.Region, po.Ship_Method
  HAVING DateAdd('d',Min(lt.Intransit_LT),po.[EX-FACTORY DATE])<(select top 1 AOD from tbl_Date))

Upvotes: 1

Gustav
Gustav

Reputation: 55806

Try using:

DateAdd('d',Min(lt.Intransit_LT),po.[EX-FACTORY DATE]) AS [Offset]

... 

WHERE ((ist.SKU Is Not Null) 
AND DateAdd('d',Min(lt.Intransit_LT),po.[EX-FACTORY DATE])<(select top 1 AOD from tbl_Date))

Upvotes: 0

Related Questions