Switch() syntax error (1 Viewer)

[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

 

[Pat Hartman]

I haven't tried to use the Switch() function this way but I would guess that the problem is that it is intended to use literal strings rather than column names. You might try qualifying the column names with the table names but no guarantees. -- [projections].[Bnkr]

But, the IIf() proposed by RV is simpler.