Computed Columns in Access 2007 Query (adp/SQL)

[Ian Bateman]

I cannot find anything in the help files to assist me with computed columns
in a Access Queries using adp/SQL. For example when using Access (mdb) I
could put Member:iif([Paid]>0,"YES","NO"), which would result in a column
called Member with a value of either Y or N depending on the value of [Paid].

This cannot be done using adp/SQL. The query designer simply puts quotes
around the entire statement and outputs that. Please could you tell me where
I can I find a useful reference guide that will tell me how to construct
computed columns in Access queries using adp/SQL?

Many Thanks
Ian

 

[Gary Walter]

I cannot find anything in the help files to assist me with computed columns
in a Access Queries using adp/SQL. For example when using Access (mdb) I
could put Member:iif([Paid]>0,"YES","NO"), which would result in a column
called Member with a value of either Y or N depending on the value of
[Paid].

This cannot be done using adp/SQL. The query designer simply puts quotes
around the entire statement and outputs that. Please could you tell me
where
I can I find a useful reference guide that will tell me how to construct
computed columns in Access queries using adp/SQL?

Hi Ian,

There is no "IIF" in SQL Server,
one usually resorts to the CASE stmt

CASE WHEN condition THEN a ELSE b END

In grid of VIEW designer the "columns"
are "down" instead of "across" so "alias"
has its own column (instead of having to use
"alias: xxxx")

Columns
Alias
CASE WHEN [Paid]>0 THEN "Yes" ELSE "No" END Member

I usually add CASE stmts last because designer
cannot reconcile its "Diagram" window (showing
all the tables and joins) with a CASE stmt, so you
no longer will get your "Diagram" window.

good luck,

gary