Combo Box with IIF Statements?

[Kumba1]

I have a query that shows items from one table, and related them to an Order # from another field... the problem is this...

In the Item database, I use a field called "Status" which contains a numerical value... it's a key... so something that has a key of 1 for instance, is considered to be a stock item, and something that has a key of 2, is a sold item, something that is 3 is a warranty, and so on for various item status'... I'm trying to have a combo box that will show me the Item ID, the Job Track Number, and the Order number... if the item is status code 1, I want the order number field to show "STK" instead of just nothing, or if the status code is 2, I want it to return the value from qrySalesTrack.OrderNumber, or is the status code is 2 I want it to display "Warranty"... any ideas? Below is the same SQL that I have so far... the query "qrySalesTrack" just relates the TN to the Sales Order

SELECT tblItem.TrackingNumber AS [Track #], tblItem.ItemID AS [Item ID], qrySalesTrack.OrderNumber AS [Order #], tblItem.Location, tblItem.Status
FROM tblItem LEFT OUTER JOIN
qrySalesTrack ON tblItem.TrackingNumber = qrySalesTrack.TrackingNumber
ORDER BY tblItem.TrackingNumber

I'm using Access XP on SQL 2000... any input would be much appreciated

 

[TheWebDataGuy]

Try this. You can add more conditions in the CASE statement.

SELECT tblItem.TrackingNumber AS [Track #], tblItem.ItemID AS [Item ID],
OrderNumber=
CASE Status
WHEN '1' THEN 'STK'
WHEN '3' THEN 'Warranty'
ELSE qrySalesTrack.OrderNumber
END,
tblItem.Location, tblItem.Status
FROM
tblItem LEFT OUTER JOIN qrySalesTrack ON tblItem.TrackingNumber = qrySalesTrack.TrackingNumber
ORDER BY tblItem.TrackingNumber

 

[Kumba1]

Very Awesome... worked like a charm... only learning curve was to turn off the graphical parts of the query builder to get it to work... Many thanks sir...