IIf Statement in ADP

  • Thread starter Thread starter Dan M
  • Start date Start date
D

Dan M

It would appear that using an IIF statement as part of the
SELECT statement no longer works and is no longer a
recognized function. Is there a workaround?

In Access 97, I relied on IIF in many list boxes, to
display the contents of a Yes/No field in a more
meaningful way. My column would be selected as...
SELECT IIF([tblEmployee].[Terminated] = -1, "X", "") AS
Terminated

It displayed a column titled Terminated and displayed an X
for every employee whose Terminated field was set to true,
and displayed nothing otherwise. Migrating to XP, using
an ADP with SQL Server back end, this function seems to be
illegal. Oh please, tell me there's a way to manufacture
columns on the fly in a SQL statement.
 
Yup!

You need to substuite Access's IIF() with SQL's much more versitle CASE()
function

SELECT CASE [tblEmployee].[Terminated]
WHEN -1 THEN 'X'
ELSE ''
END AS Terminated

Above is but one example for using CASE() Take a look at Sql's BOL for more
info.

Ron W
 
Thanks Ron, but I don't see how your answer applies to my
problem. I am not writing VBA code, I am building a SQL
string to use as the rowsource of a list box. A SQL
string doesn't appear to support VBA's CASE function.
SELECT CASE isn't even part of Access Help, while it is
only found in VBA Help. I don't think this is my solution.
-----Original Message-----
Yup!

You need to substuite Access's IIF() with SQL's much more versitle CASE()
function

SELECT CASE [tblEmployee].[Terminated]
WHEN -1 THEN 'X'
ELSE ''
END AS Terminated

Above is but one example for using CASE() Take a look at Sql's BOL for more
info.

Ron W

Dan M said:
It would appear that using an IIF statement as part of the
SELECT statement no longer works and is no longer a
recognized function. Is there a workaround?

In Access 97, I relied on IIF in many list boxes, to
display the contents of a Yes/No field in a more
meaningful way. My column would be selected as...
SELECT IIF([tblEmployee].[Terminated] = -1, "X", "") AS
Terminated

It displayed a column titled Terminated and displayed an X
for every employee whose Terminated field was set to true,
and displayed nothing otherwise. Migrating to XP, using
an ADP with SQL Server back end, this function seems to be
illegal. Oh please, tell me there's a way to manufacture
columns on the fly in a SQL statement.
Click to expand...


.
Click to expand...
 
Hi,

MS SQL Server has a SQL-CASE .... syntax, indeed, but a little bit
confusing, let me compare it with the SWITCH-VBA function:


vba:
SWITCH( condition1, value1,
condition2, value2,
..., ...,
TRUE, defaultValue )


MS SQL Server SQL:

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE defaultValue END


It is NOT a control of the flow of execution, it behaves like a function
(well, a statement in fact) in that it returns just one value and cannot be
use elsewhere a value is expected. Note that if all the conditions are of
the kind:

someExpression = testValue

you can use

CASE someExpression
WHEN testValue1 THEN value1
WHEN testValue2 THEN value2
...
ELSE defaultValue END



Example, in pubs:

SELECT SUM(CASE WHEN state='CA' THEN 1 ELSE 0 END) As CountFromCA,
SUM(CASE WHEN state='OR' THEN 1 ELSE 0 END) As CountFromOR FROM dbo.authors


is a possible way to count how many records are with state='CA' and those
where state='OR', kind of crosstab. Sure, an alternative is

SELECT state, COUNT(*) FROM dbo.authors WHERE state IN( 'CA', 'OR') GROUP BY
state

but the result is then "not tabular" .



In your exact 'case', Ron's solution should work.



Hoping it may help,
Vanderghast, Access MVP



Dan M said:
Thanks Ron, but I don't see how your answer applies to my
problem. I am not writing VBA code, I am building a SQL
string to use as the rowsource of a list box. A SQL
string doesn't appear to support VBA's CASE function.
SELECT CASE isn't even part of Access Help, while it is
only found in VBA Help. I don't think this is my solution.
-----Original Message-----
Yup!

You need to substuite Access's IIF() with SQL's much more versitle CASE()
function

SELECT CASE [tblEmployee].[Terminated]
WHEN -1 THEN 'X'
ELSE ''
END AS Terminated

Above is but one example for using CASE() Take a look at Sql's BOL for more
info.

Ron W

Dan M said:
It would appear that using an IIF statement as part of the
SELECT statement no longer works and is no longer a
recognized function. Is there a workaround?

In Access 97, I relied on IIF in many list boxes, to
display the contents of a Yes/No field in a more
meaningful way. My column would be selected as...
SELECT IIF([tblEmployee].[Terminated] = -1, "X", "") AS
Terminated

It displayed a column titled Terminated and displayed an X
for every employee whose Terminated field was set to true,
and displayed nothing otherwise. Migrating to XP, using
an ADP with SQL Server back end, this function seems to be
illegal. Oh please, tell me there's a way to manufacture
columns on the fly in a SQL statement.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top