CASE WHEN In Access SQL

[damjanu]

Hi

I am trying to run this query in Acces database file

SELECT
......,
CASE
WHEN p.name IS NULL THEN r.name
ELSE r.name & " >> " & p.name (or I also tried ELSE r.name + 'END AS full_name
FROM myTable

But I get Error message: "Syntax error (missing operator) in query
expression 'CASE WHEN'",
and WHEN is highlighted.

Is it true that Access SQL does not support CASE WHEN?
Does anyone know what to use instead, or how to get arround this
problem?
Also is there a list of features that don't work in Acces SQL?
(I looked in help, but found nothing)

Thanks a lot

Damjan

 

[Dirk Goldgar]

Hi

I am trying to run this query in Acces database file

SELECT
......,
CASE
WHEN p.name IS NULL THEN r.name
ELSE r.name & " >> " & p.name (or I also tried ELSE r.name + '
END AS full_name
FROM myTable

But I get Error message: "Syntax error (missing operator) in query
expression 'CASE WHEN'",
and WHEN is highlighted.

Is it true that Access SQL does not support CASE WHEN?
Does anyone know what to use instead, or how to get arround this
problem?
Also is there a list of features that don't work in Acces SQL?
(I looked in help, but found nothing)

Thanks a lot

Damjan

Jet SQL doesn't support CASE WHEN. Use IIf or the VBA functions Switch
or Choose instead. The help file has a comparison of Microsoft Jet SQL
and ANSI SQL, but it doesn't have a specific comparison between Jet and
other dialects.

 

[Arvin Meyer]

Yes, it's true, BUT. even far more powerfully, the Jet Expression Service
support full usage of VBA in SQL queries. That means you can write far more
complex Select Case statements than you could ever dream of before, within a
VBA function, and use that function in your Access query.

Practically anything that can be written in a VBA function can be used in an
Access query.

 

[Tom Ellison]

Dear Damjan:

It would seem you are writing for the Jet database engine, not the
MSDE database engine (both of which come with Access). Jet does not
support CASE. The IIf() is typically used:

IIf(p.name IS NULL, r.name, r.name & " >> " & p.name) AS full_name

As for a "list of features that don't work" that's a tough one. Do
you want all the features of ANSI SQL that don't work. Since there
are a lot of features that no one has implemented, this would be quite
a list.

I suppose your list would be relative to some other product with which
you are familiar. But as I don't know that, and I don't know of any
such lists, I don't think I can help there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Albert D. Kallal]

You can use the fact that nulls propagate in expressions when you use + to
concatenations.
Nulls DO NOT propagate when you use "&" to concatenate. When you deal with
non-nulls, then you can use & or +

So, really, all you need is:

select (r.name & (" >> " + p.name)) as full_name from myTable

The above will work just fine.

if p.name is null, then the ">>" will also be dropped.

if we use

(" >> " & p.name)

Then, the " >> " will appear, even WHEN p.name is null