SQL Question

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

Dan M

Using Access XP project with SQL Server 2K data.

I need a way to manipulate the output of my queries and/or
SQL statements to display conditional output. In my
Access 97 Jet database, I could insert an IIF statement to
control output based on the result of a test condition.
XP adp files don't seem to have this ability.

As a SQL statement for a list box, this used to work:
SELECT EmployeeName, IIF([Terminated]=-1, 'X', '') As
Active FROM tblEmployees;

If I attempt to insert the IIF condition in the Access
QBE, it converts the whole thing to a literal string. If
I insert the above SQL string manually, it
returns "Invalid SQL Statement. Check the server filter..."

If the IIF statement is no longer available for
conditionally outputting information in a listbox column,
is there another way? And remember, this has nothing to
do with VBA, so SELECT CASE won't work.
 
Hi Dan

In this case you could use the Format property of the field, or of the
control on the form where it is displayed. Use the format:
;\X;'';
to display "X" if the field is True, otherwise blank.
 
Hi,

I just tried out the CASE statement in an Access ADP against SQL Server and
it worked OK.

Syntax would be something like

SELECT EmployeeName,
CASE
WHEN (Terminated=-1) THEN 'X'
ELSE 'A'
END
As Active
FROM tblEmployees

Cheers,
Peter
 
Format? To return a value if true or false? I can't even
get the Format function to be accepted in the SQL
statement. It returns "Format is not a recognized
function name". In Access Help, Format only seems to be
supported as a display property for a field or control,
not for the output column of a query or SQL statement.
-----Original Message-----
Hi Dan

In this case you could use the Format property of the field, or of the
control on the form where it is displayed. Use the format:
;\X;'';
to display "X" if the field is True, otherwise blank.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dan M said:
Using Access XP project with SQL Server 2K data.

I need a way to manipulate the output of my queries and/or
SQL statements to display conditional output. In my
Access 97 Jet database, I could insert an IIF statement to
control output based on the result of a test condition.
XP adp files don't seem to have this ability.

As a SQL statement for a list box, this used to work:
SELECT EmployeeName, IIF([Terminated]=-1, 'X', '') As
Active FROM tblEmployees;

If I attempt to insert the IIF condition in the Access
QBE, it converts the whole thing to a literal string. If
I insert the above SQL string manually, it
returns "Invalid SQL Statement. Check the server filter..."

If the IIF statement is no longer available for
conditionally outputting information in a listbox column,
is there another way? And remember, this has nothing to
do with VBA, so SELECT CASE won't work.
Click to expand...


.
Click to expand...
 
Hi Dan

I wasn't suggesting you use the Format statement in SQL, but that you return
the field intact and then Format the resulting value in the form where you
are displaying the result of the query.

If you really need to change the *value* of the returned field, depending in
certain criteria, then, as Peter Hoyle has pointed out, you can use the SQL
CASE function (not to be confused with the VBA Select Case statement).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dan M said:
Format? To return a value if true or false? I can't even
get the Format function to be accepted in the SQL
statement. It returns "Format is not a recognized
function name". In Access Help, Format only seems to be
supported as a display property for a field or control,
not for the output column of a query or SQL statement.
-----Original Message-----
Hi Dan

In this case you could use the Format property of the field, or of the
control on the form where it is displayed. Use the format:
;\X;'';
to display "X" if the field is True, otherwise blank.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dan M said:
Using Access XP project with SQL Server 2K data.

I need a way to manipulate the output of my queries and/or
SQL statements to display conditional output. In my
Access 97 Jet database, I could insert an IIF statement to
control output based on the result of a test condition.
XP adp files don't seem to have this ability.

As a SQL statement for a list box, this used to work:
SELECT EmployeeName, IIF([Terminated]=-1, 'X', '') As
Active FROM tblEmployees;

If I attempt to insert the IIF condition in the Access
QBE, it converts the whole thing to a literal string. If
I insert the above SQL string manually, it
returns "Invalid SQL Statement. Check the server filter..."

If the IIF statement is no longer available for
conditionally outputting information in a listbox column,
is there another way? And remember, this has nothing to
do with VBA, so SELECT CASE won't work.
Click to expand...


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