Transforming an IF...Then to Case...When

  • Thread starter Thread starter Michiel via AccessMonster.com
  • Start date Start date
M

Michiel via AccessMonster.com

Hi,

I try to converet this simple If...Then to a Case...When but get error
mesaages.
Anyone can help me to pinpoit my error?

IF...THEN (which works)
select IIF(Left(isin, 2)= 'US', 'America', 'Non US')
from stub_internal

CASE..WHEN (with errors)
select CASE WHEN left(isin, 2)= 'US'
THEN 'America'
ELSE 'Non US'
END as Origin
from stub_internal

Thanks!
 
Hi -

Your IF - THEN as posted won't work either - you'll get a compilation error.
If you are only dealing with two options ( US or not), then the IF - THEN -
ELSE structure is fine, but if you really want the Select Case , it will look
something like this:

Select Case Left(isin, 2)
Case "US"
'
' Do code for USA
'
Case Else
'
' Do code for NON- USA
'
End Select

What are you trying to do though - set up a SQL statement?

HTH

John

Hi,

I try to converet this simple If...Then to a Case...When but get error
mesaages.
Anyone can help me to pinpoit my error?

IF...THEN (which works)
select IIF(Left(isin, 2)= 'US', 'America', 'Non US')
from stub_internal

CASE..WHEN (with errors)
select CASE WHEN left(isin, 2)= 'US'
THEN 'America'
ELSE 'Non US'
END as Origin
from stub_internal

Thanks!
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 
Michiel,

I'm not sure, but your Case...When statement looks fine. The only thing I
would do is surround the Case...When in parentheses:

select
(
CASE
WHEN left(isin, 2)= 'US'
THEN 'America'
ELSE 'Non US'
END
) as Origin
from stub_internal

HTH,

Conan
 
Micheil,

Is this a Pass-through query to SQL Server (or other DB system)?

The following is a reply from John Vinson to another person's question that
might be similar to yours:


----------------------------------------------
You don't. Case is not supported in JET SQL.

Use the Switch() function instead.
--

John W. Vinson [MVP]






HTH,

Conan
 
Yes I intend to make a query and translate some codes into human friendly
output.

M.
 
In fact the query displayed calls a real MS Access table.
But my intention is to extend the query to a linked table of a Pervasive
Database.

Considereing the answer and the fact that I need to evaluatie many values I'm
afraid I will have to make a 20-level nested If...Then structure.

Thanks!

ML.
 
Yes I intend to make a query and translate some codes into human friendly
output.
Click to expand...

If it's more than four or five values, I'd really set up a translation table
and join it in a Query.

To do this with Switch you can use

ShowValue: Switch(
Code: 
 = "AC", "Accepted", [Code] = "RJ", "Rejected", [Code]
= "WTF", "I don't think you meant to use that code")

You can also set "SQL Server Compatible Syntax (ANSI 92)" in Tools...
Options... Tables and Queries; note that this is a database-wide setting and
may well break existing queries.
 
Back
Top