a SQL "If" query

[dsundy2003]

Hi
I'm looking to write a View in sql (using enterprise manager) which does pretty much what Access queries do:

The syntax in access is: SELECT IIf([insstdte]<#10/1/2004#,"Now till Sep 2004","After Sept 2004") AS Expr1 FROM .....

Now how the heck can I say that in a SQL query, using a sql server database.
Pls help

(I know its not strictly an MS Access question, but its kinda related...)

Darryn

 

[jmersing]

Can you create an OBDC connection to the SQL server? Because if so you can query in Access SQL syntax. The SQL should be the same unless you are trying to run a "Pass through" query or are using Oracle.

To create the connection in Acces just goto tables-Link, then hit the dropdown at the bottom of the dialog box and scroll down to OBDC databases.

Then enter the DSN information and you should be all set

 

[dsundy2003]

Thanks Jmersing

Does that mean that sql does not have an 'iif' function like access does ?
Surely not ...

 

[jmersing]

Yes it does tthe syntax is like this

select iif([yourfieldname]>1,"Yes","No") as answer
from yourtable;

 

[dsundy2003]

I created a new View and used your syntx that you sugested:
SELECT iif([user id] = 'Charlotte', " Its Charlotte","Its Not Charlotte") AS answer
FROM dbo.['raw data'];

But I still get this error "Error in list of function arguments: '=' not recognized.
Unable to parse query text."

Help !

Darryn

 

[jmersing]

You should have double quotes around Charlotte


SELECT iif([user id] = "Charlotte", " Its Charlotte","Its Not Charlotte") AS answer
FROM dbo.[raw data];

and no single quotes around your table name

what is dbo ? seems like a strange name for a table