Filter Combo Box with YES/NO Fields when item is discontinued

[jordanturner]

Hi,

I have recently added yes/no field to stock items to show when they are discontinued and appear as check boxes on the stock form. I have a purchase order form with a combo box to select items for ordering. The items displayed in the combo box are also filtered by supplier, so when a supplier is selected, then only items from that supplier are displayed. The combo box is based on a query.

I would like to filter the combo box so that a product will not be displayed in the list if it is discontinued. I was thinking I could do this with an SQL iff statement but am unsure how the code should look.

The current code for the combo box is:

SELECT tblStock.StockID, tblStock.ItemName, tblStock.SupplierID
FROM tblSupplier INNER JOIN tblStock ON tblSupplier.SupplierID=tblStock.SupplierID
WHERE (((tblStock.SupplierID)=[forms]![frmPurchaseOrderBT]![SupplierCombo]))
ORDER BY tblStock.ItemName;

Any ideas would be very helpful

Thank you

 

[gemma-the-husky]

can't you design a query as a visual query. drag the "flag" to the query design, and sleect true or false as the appropriate criterion.

then you could see what the sql looked like