Monterey
asked on
SQL If Statement in Access Query
I'm not sure what I am doing wrong, but here is my SQL Code within my Access Query:
SELECT Count(qryVendorX.[Item #]) AS [TotalItems]
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName
HAVING (((qryVendorX.VendorNumber ) = [forms]![frmPOTransHeader] .[Vendor #]))
IIF (qryVendorX.[TotalItems])> 0
SELECT qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description, qryVendorX.[Vendor Item #]
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description, qryVendorX.[Vendor Item #]
HAVING (((qryVendorX.VendorNumber )=[forms]! [frmPOTran sHeader].[ Vendor #]))
ELSE
SELECT tblItemMaster.[Item #], [Department] & "-" & [Account] & "-" & [Sub-Account] AS [G/L Account], tblItemMaster.Description
FROM tblItemMaster
ORDER BY tblItemMaster.Description;
My goal is to display a list of items specific to vendors if the information is in the Vendor Cross Reference Table. If the Vendor is not in the cross reference table, just display all Items. Any help is greatly appreciated.
Paula
SELECT Count(qryVendorX.[Item #]) AS [TotalItems]
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName
HAVING (((qryVendorX.VendorNumber
IIF (qryVendorX.[TotalItems])>
SELECT qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description,
FROM tblItemMaster RIGHT JOIN qryVendorX ON tblItemMaster.[Item #] = qryVendorX.[Item #]
GROUP BY qryVendorX.VendorNumber, qryVendorX.VendorName, qryVendorX.[Item #], tblItemMaster.Description,
HAVING (((qryVendorX.VendorNumber
ELSE
SELECT tblItemMaster.[Item #], [Department] & "-" & [Account] & "-" & [Sub-Account] AS [G/L Account], tblItemMaster.Description
FROM tblItemMaster
ORDER BY tblItemMaster.Description;
My goal is to display a list of items specific to vendors if the information is in the Vendor Cross Reference Table. If the Vendor is not in the cross reference table, just display all Items. Any help is greatly appreciated.
Paula
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No problem. You are most welcome.
ASKER