Query with IIF Results?

[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... 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

 

[Kumba1]

I also forgot to mention, i'm using MS Access XP on an SQL 2000 Server (ADP)... Thanks...

 

[TonyJollans]

Hi Kumba1,

As this is the Access Queries and Jet SQL Forum, I will give you the way to do it in Jet:

[blue][tt]SELECT ..., IIF(tblItem.Status=1,"STK",mqrySalesTrack.OrderNumber) AS [Order #], ...[/tt][/blue]

I believe, in SQL Server you do something like ..

[blue][tt]SELECT ..., (CASE WHEN tblItem.Status = 1 THEN "STK" ELSE mqrySalesTrack.OrderNumber) AS [Order #], ...[/tt][/blue]

But you would be as well checking in a more relevant Forum, such as the Microsoft: Access Project (ADP) Forum.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.