Switch() syntax error

[mj76]

I am normally a SQL server developer and can knock out sprocs pretty easily, however, now I have to implement a SQL sproc into an access query.

I know I need to use the switch function, and have been trying, however, I am getting errors. Here is the scenario I am trying to accomplish:

1. Banker Name gets passed as a parameter
2. Banker Name value is evaluated against two fields (banker, backup). If the Banker Name is the same as the banker field I want an aliased column (bnkr) to equal the banker field, otherwise, I want the aliased column to equal the value in the backup field.

Here is the syntax I have tried, to no avail:

Select = Switch([bnkr]=[Banker Name], [bnkr] , [Backup] = [Banker Name],[Backup], True, [Bnkr])
as bnker,
bnkr, [backup], deal, close_month, year
from projections

where year = [cyear] and ( banker = [banker name] or backup = [banker name])

the reason this is failing is that it is only returning records where [banker name] is in the backup field, not both the banker and backup fields.

If someone can look at this and let me know how to have it so it returns both, I would be extremely grateful.

Thank you very much

mj

 

[RV]

>I know I need to use the switch function<

Not necessarily.
You could use the IIF function:

SELECT IIF([Banker Name]=banker,banker,[Backup]) AS bnkr, [backup], deal, close_month, year
from projections
where year = [cyear];

Greetings,

RV