if in jete SQL

[Guest]

Hi,
is there anything like 'if' or 'case' in jet SQL
thanx

alek_mil

 

[Van T. Dinh]

Nope but in JET, you can use VBA functions like IIf(), Switch(), Choose()
....

Check Access VB Help on these function.

 

[Douglas J Steele]

Just a warning, though.

I believe you can only use VBA functions like Switch and Choose if you're
running the queries from within Access. If you're trying to run a query from
outside of Access, the queries won't work.

 

[Brendan Reynolds]

Hi Doug,

I used to think this too, but in fact most built-in VBA functions will work
in Jet queries executed outside of the Microsoft Access environment. Custom,
user-defined functions will not work, of course, and neither will functions
such as NZ, which are members of the Access object library rather than of
the VBA object library.

I once set out to compile a list of functions that do work in Jet queries
executed outside of the Microsoft Access environment, but never finished it,
and don't expect I ever will now - in hindsight, it was the wrong approach -
I should have compiled a much smaller list of the functions that *don't*
work!

The uncompleted list is at http://brenreyn.blogspot.com

 

[Douglas J Steele]

Thanks, Brendan. I looked at your blog once a while ago: I should go back!

 

[Brendan Reynolds]

Don't expect too much! I haven't posted anything there for a long time - I
ran out of time, and, to be honest, motivation! But in the context of
this thread, it may be useful to someone.

 

[Ed Warren]

And the light slowly dawned!!
I have been confused by this, sometimes functions will work, sometimes
not!!
Your explanation makes it clear, thanks

Ed Warren