SQL Incorrect Syntax Error near '<' 1

[rwn]

I'm using thi SQL and when it runs it states:
Incorrect syntax near '<'.

A'm I overlooking something that is obvious? Thank you in advance!

SELECT Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Material.Material, Material.Order_Point, Material.Reorder_Qty, IIf([On_Hand_Qty]<=[Order_Point],"Time to ReOrder","Inv OK") AS Expr1, Material.Class
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
GROUP BY Material.Material, Material.Order_Point, Material.Reorder_Qty, IIf([On_Hand_Qty]<=[Order_Point],"Time to ReOrder","Inv OK"), Material.Class
HAVING (((IIf([On_Hand_Qty]<=[Order_Point],"Time to ReOrder","Inv OK"))="Time to ReOrder"));

 

[dhookom]

Not sure if this is the exact logic required since you are summing the On_Hand_Qty but not comparing the sum with the Order_Point. You might also have issues if either of some of the fields are Null.

Try:

SELECT Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Material.Material, Material.Order_Point, Material.Reorder_Qty, IIf([On_Hand_Qty]<=[Order_Point],"Time to ReOrder","Inv OK") AS Expr1, Material.Class
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE [On_Hand_Qty]<=[Order_Point]
GROUP BY Material.Material, Material.Order_Point, Material.Reorder_Qty, IIf([On_Hand_Qty]<=[Order_Point],"Time to ReOrder","Inv OK"), Material.Class;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Due the criteria, why not simply this ?

SELECT Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Material.Material, Material.Order_Point, Material.Reorder_Qty, "Time to ReOrder" AS Expr1, Material.Class
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE [On_Hand_Qty]<=[Order_Point]
GROUP BY Material.Material, Material.Order_Point, Material.Reorder_Qty, Material.Class

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH,
Good catch!

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Thanks again to all, I will try. What was the Good catch the PHV accomplished, being a newbie I'm not seeing the obvious.

 

[rwn]

OH, I see an interesting point. Reorder point needs to compare against the On_Hand_Qty.

is this correct then...

WHERE [Sum(Material_Location.On_Hand_Qty)]<=[Order_Point]

 

[PHV]

SELECT Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Material.Material, Material.Order_Point, Material.Reorder_Qty, "Time to ReOrder" AS Expr1, Material.Class
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
GROUP BY Material.Material, Material.Order_Point, Material.Reorder_Qty, Material.Class
HAVING Sum([On_Hand_Qty])<=[Order_Point]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886