Trying to create a Case Statement with multiple criteria to return multiple values

[RunTime91]

Greetings All!



I'm close!! (I think )

Just trying to create a Case Statement that has three criteria and returns three values.

This works great but I can't seem to figure out how to go to the next case statement if the first value is false

SELECT CASE WHEN LEFT(A.SalesID, 3) ='AAA'THEN'Boo'END As Poo, IIf(Sales ='R','Rural','City') AS Boo2, IIf(Del ='S','Standard','Overnight') AS Boo3
FROM Dbo.MTable A JOIN Dbo.Delivery B
ON A.SalesID = B.SalesID
WHERE B.Tracking_Number ='111-123456'

The only other CASE will be when LEFT(A.SalesID, 3) = 'BBB' the other criteria will remain the same as below

SELECT CASE WHEN LEFT(A.SalesID, 3) ='BBB' THEN 'Boo' END As Poo, IIf(Sales ='R','Rural','City') AS Boo2, IIf(Del ='S','Standard','Overnight') AS Boo3
FROM Dbo.MTable A JOIN Dbo.Delivery B
ON A.SalesID = B.SalesID
WHERE B.Tracking_Number ='111-123456'

So I guess what I am trying to do is somehow combine these statements?

And yes the codes does come from SSMS - I like to develop in SSMS then move it over to Access VBE

Thanks for any help~

[pbaldy]

Untested

CASE LEFT(A.SalesID, 3) 
  WHEN 'AAA' THEN 'Boo'
  WHEN 'BBB' THEN 'xyz'
END

[RunTime91]

Hey Paul ~ Thanks for being out there

I tried the following - I got red sqwigglys on the second WHEN Statement

SELECT CASE LEFT(A.SalesID, 3)
WHEN 'AAA' THEN 'Boo' END As Poo, IIf(Sales ='R','Rural','City') AS Boo2, IIf(Del ='S','Standard','Overnight') AS Boo3
WHEN 'BBB' THEN 'Poo' END As Boo, IIf(Sales ='R','Rural','City') AS Boo2, IIf(Del ='S','Standard','Overnight') AS Boo3
FROM Dbo.MTable A JOIN Dbo.Delivery B
ON A.SalesID = B.SalesID
WHERE B.Tracking_Number ='111-123456'

[RunTime91]

Trying a new approach

I'm just going to use two IIf Statements (if I can)

Here is one of the IIf Statements - What I cannot get to work is getting the SELECT Query to populate the Me.CmboUrgRou and the Me.CmboRqstType Objects
Yes the code looks different - I'm done trying to alias it

If (left(Me!txtQKey, 3) = "XAW") Then

  StrSQL = "SELECT IIf(A.Split = 'R', 'Referral', 'Authorization') As Boo, IIf(A.UrR = 'R', 'Routine', 'Urgent') As Poo " & _
            "FROM Dbo.AQue A JOIN Dbo.Assignments B " & _
            "ON A.QKey = B.QKey " & _
            "WHERE B.EDI_Tracking_Number = '" & Me.TxtCaseRef & "'"

   Debug.Print StrSQL

conn.Execute (StrSQL)

Me.CmboCivMTF = "Civilian"
Me.CmboRqstType = Boo
Me.CmboUrgRou = Poo

The Cmbo UrgRou & RqstType keep equaling NULL

Thank you so much...

[pbaldy]

In the first, the CASE statement was ended at "... As Poo," so the second WHEN is incomplete. I don't really understand your goal, but in the second effort I think you'd have to open a recordset on the SQL, not execute it. Then:

Me.CmboRqstType = rs!Boo

I don't use ADO much, so I'd probably set a pass through query to the SQL and open a DAO recordset on that query.