VS2008 : Error Using IIF in Select Query

[Screaming Eagles 101]

Hi,

Using Visual Studio 2008:

I have a tableadapter in my dataset connecting to a table in Microsoft
Access Database,
which forces me to use the IIF statement rather than the CASE statement in
my query
which Access doesn't allow (unfortunately....)

Using this statement for example : Select IIF(true, 'yes', 'no') as Test,
etc.... from ....
works fine, as long as I don't use comparative signs as =, >, < ...
It seems like a boolean test is not allowed there .... :-( ?

So, if I'm writing

Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from ....
or
Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from ....
or
Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from ....

I'm putting myself in a freaky 'error' show, getting a message
"Error in list of function arguments: '=' not recognized. Unable to parse
query text."

How can I perform that boolean test in the Select statement with IIF,
and not getting the error from the Jet engine ?

--
Filip
http://www.ww2airborne.net/
Official Site of the 101st Airborne - 463rd PFA
skype: airborne463pfa-fiwi
[It's nice to be important, but it's more important to be nice!]
----------------------------------------------------------------

 

[Clive Lumb]

Hi,

Using Visual Studio 2008:

I have a tableadapter in my dataset connecting to a table in Microsoft
Access Database,
which forces me to use the IIF statement rather than the CASE statement in
my query
which Access doesn't allow (unfortunately....)

Using this statement for example : Select IIF(true, 'yes', 'no') as Test,
etc.... from ....
works fine, as long as I don't use comparative signs as =, >, < ...
It seems like a boolean test is not allowed there .... :-( ?

So, if I'm writing

Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from ....
or
Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from
....
or
Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from ....

I'm putting myself in a freaky 'error' show, getting a message
"Error in list of function arguments: '=' not recognized. Unable to parse
query text."

How can I perform that boolean test in the Select statement with IIF,
and not getting the error from the Jet engine ?

I don't think it is the Jet Engine that is throwing the error.
If you try using such an IIF statement in the Query builder it throws the
error immediately. I ththrows it again if you use "Test Query", BUT the
results are there.

VBhelper shows an example (with VB6 unfortunately - no idea if it will work
in VS2008) where the IIF part of the query is put in a string variable
before building the SQL - this might trick the parser into accepting it?

Good luck!

 

[Clive Lumb]

Hi,

Using Visual Studio 2008:

I have a tableadapter in my dataset connecting to a table in Microsoft
Access Database,
which forces me to use the IIF statement rather than the CASE statement in
my query
which Access doesn't allow (unfortunately....)

Using this statement for example : Select IIF(true, 'yes', 'no') as Test,
etc.... from ....
works fine, as long as I don't use comparative signs as =, >, < ...
It seems like a boolean test is not allowed there .... :-( ?

So, if I'm writing

Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from ....
or
Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from
....
or
Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from ....

I'm putting myself in a freaky 'error' show, getting a message
"Error in list of function arguments: '=' not recognized. Unable to parse
query text."

How can I perform that boolean test in the Select statement with IIF,
and not getting the error from the Jet engine ?

Sorry, forgot to ask...
Are the boolean test and yes/no results fixed? Or do they change from case
to case.
If they are fixed you could use a stored procedure in Access to do that
part of the work for you (even passing a parameter or two).

 

[Screaming Eagles 101]

Sorry, forgot to ask...
Are the boolean test and yes/no results fixed? Or do they change from case
to case.
If they are fixed you could use a stored procedure in Access to do that
part of the work for you (even passing a parameter or two).

Actualy the real thing is calculated as follows
SELECT colDevice, colAggregation, IIF(colAggregation = 'SUM', SUM(colValue),
AVG(colValue)) AS Amount FROM ... GROUP BY...
My example was to simplify things...
--
Filip
http://www.ww2airborne.net/
Official Site of the 101st Airborne - 463rd PFA
skype: airborne463pfa-fiwi
[It's nice to be important, but it's more important to be nice!]
----------------------------------------------------------------

 

[Michel Posseth [MCP]]

Filip

Access does not fully conform to the ansi-sql standards. Some functions in
Access would not work on other sql-based DBMS The function is not supported
on Sql. So I think this is the reason why it wouldn't also work on
TableAdapter Query Configuration Wizard to avoid incompatibilities with
other database systems. I guess what you should do is to just use the
functions available in ansi-sql to mimic the behavior of the functions in
Access.

Other solutions might be to use a command object or call a Access query in a
stored procedure kind of way

hth

Michel Posseth