By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,556 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Problems with IIF

P: n/a
Hello,

I am trying to use the following expression in a stored procedure:

iif(something>0, 0,1)

when I try the procedure I get an error message saying

ADO Error: 'iif' is not a recognized function name

Doesn't iif work in all versions of Access ? I am using Access 2002
and works towards a MS 2000 SQL server.

Gunnar
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Gunnar Liknes" <gl*****@global-satcom.com> wrote in news:40262082$1
@news.broadpark.no:
Hello,

I am trying to use the following expression in a stored procedure:

iif(something>0, 0,1)

when I try the procedure I get an error message saying

ADO Error: 'iif' is not a recognized function name

Doesn't iif work in all versions of Access ? I am using Access 2002
and works towards a MS 2000 SQL server.

Gunnar


****************************
From the VBA Object Browser:
****************************

Function IIf(Expression, TruePart, FalsePart)
Member of VBA.Interaction
**************************
From the JET SQL Reference
**************************

The Microsoft Jet database engine uses the Microsoft® Visual Basic® for
Applications (or VBA) expression service to perform simple arithmetic and
function evaluation. All of the operators used in Microsoft Jet SQL
expressions (except Between, In, and Like) are defined by the VBA expression
service. In addition, the VBA expression service offers over 100 VBA
functions that you can use in SQL expressions. For example, you can use these
VBA functions to compose an SQL query in the Microsoft Access query Design
view, and you can also use these functions in an SQL query in the DAO
OpenRecordset method in Microsoft Visual C++®, Microsoft Visual Basic, and
Microsoft Excel code.

***************************
FROM Transact SQL Reference
***************************

CASE
Evaluates a list of conditions and returns one of multiple possible result
expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple
expressions to determine the result.
The searched CASE function evaluates a set of Boolean expressions to
determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

**********
AIR SCRIPT
**********

CASE
WHEN something > 0 THEN 0
ELSE 1
END

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2

P: n/a
IIf is to access what Case is to sql server
stored procedures run on sql server

CASE something
WHEN > 0 THEN 0 ELSE 1
END

"Gunnar Liknes" <gl*****@global-satcom.com> wrote in message news:<40********@news.broadpark.no>...
Hello,

I am trying to use the following expression in a stored procedure:

iif(something>0, 0,1)

when I try the procedure I get an error message saying

ADO Error: 'iif' is not a recognized function name

Doesn't iif work in all versions of Access ? I am using Access 2002
and works towards a MS 2000 SQL server.

Gunnar

Nov 12 '05 #3

P: n/a
> "Roger" wrote
"Gunnar Liknes" wrote I am trying to use the following expression in a stored procedure:

iif(something>0, 0,1)

when I try the procedure I get an error message saying

ADO Error: 'iif' is not a recognized function name

Doesn't iif work in all versions of Access ? I am using Access 2002
and works towards a MS 2000 SQL server.
IIf is to access what Case is to sql server
stored procedures run on sql server

CASE something
WHEN > 0 THEN 0 ELSE 1
END


If I try to put in a CASE statement in a column I get an error message
saying "This designer does not graphically support the CASE SQL
costruct" (and I cannot save the procedure).

I guess I have to start looking for some updates/addons then....

Thanks to all repliers:-)

Gunnar
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.