Use CASE Statements in Access 2

[uniroyal2001]

Hello All,
I am trying to use this mentioned CASE statement in a Access query.
Any leads in this matter is greatly appreciated.

SELECT 
VALUE(G.TEAM,'    ') AS CBT_TEAM,
VALUE(L.PSYS_COLLECTOR_ID,'   '),
VALUE(L.PSYS_TEAM_ID,'    ') AS PSYS_TEAM,
H.LEGAL_NAME AS CUSTOMER_NAME,
CASE
    WHEN L.PSYS_COLLECTOR_ID =  'CTA'
     AND G.TEAM              <> '    '   THEN G.TEAM
    WHEN L.PSYS_COLLECTOR_ID =  'CTA'
     AND G.TEAM              =  '    '   THEN C.TEAM
    WHEN L.PSYS_COLLECTOR_ID <> '   '
     AND L.PSYS_COLLECTOR_ID <> 'CTA'    THEN L.PSYS_TEAM_ID
    WHEN G.TEAM              <> '    '   THEN G.TEAM
    WHEN C.TEAM              <> '    '   THEN C.TEAM
    ELSE                                   '????'
END AS TEAM

thanks in advance

John Pasron

 

[jebry]

Hi!

You can't use the Case statement in Access SQL. You can create a function in VBA that you send the fields to and you can use the Select Case function and return the appropriate value.

Also, I have never seen the Value function either. It looks like it might do the same thing as Access's Nz function but I can't be sure. Let me know what that function does also.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lupins46]

JET SQL does not have a CASE statement.
You could use IIF() which would get very complex, or you could create a VBA user function to give the result and call that in the query.

 

[uniroyal2001]

Any idea to just convert it in access.
The code you are seeing about is in ANSI SQL.
I tried using IIF(...)..but gave me some obscure error message.
VALUE(G.TEAM,' ') AS CBT_TEAM,
this I can convert as Nz(G.TEAM,'') AS CBT_TEAM

 

[messenger101]

Did you try using IIF() for all the cases

 

[uniroyal2001]

yes i tried using multiple IIFs but something in the syntax didnt work.

 

[cmmrfrds]

For what you are doing the SWITCH Function may work the best.
Example syntax.
switch(item="xxx",.25,item="yyy",.75,item="zzz",.55) AS rate

Switch(L.PSYS_COLLECTOR_ID = 'CTA' AND G.TEAM <> ' ',G.TEAM,L.PSYS_COLLECTOR_ID = 'CTA' AND G.TEAM = ' ',C.TEAM, etc.......


Look up in Help for default.

 

[uniroyal2001]

hello cmmrfrds,
so if I am to follow your steps then the final code should like this

Switch(L.PSYS_COLLECTOR_ID =  'CTA'  AND G.TEAM <> '    ' ,G.TEAM, L.PSYS_COLLECTOR_ID =  'CTA' AND G.TEAM =  '    ',C.TEAM, L.PSYS_COLLECTOR_ID <> '   ' AND L.PSYS_COLLECTOR_ID <> 'CTA', L.PSYS_TEAM_ID,G.TEAM <> '    ',G.TEAM,C.TEAM <> '    ' ,C.TEAM, '????') AS TEAM

Please confirm and thanks a mill for your help.

John P

 

[cmmrfrds]

That looks correct. Give it a try.

 

[fredericofonseca]

Final bit is incomplete.

Switch(
L.PSYS_COLLECTOR_ID = 'CTA' AND G.TEAM <> ' ',G.TEAM,
L.PSYS_COLLECTOR_ID = 'CTA' AND G.TEAM = ' ',C.TEAM,
L.PSYS_COLLECTOR_ID <> ' ' AND L.PSYS_COLLECTOR_ID <> 'CTA', L.PSYS_TEAM_ID,
G.TEAM <> ' ',G.TEAM,
C.TEAM <> ' ',C.TEAM,
'????') AS TEAM

switch function works on pairs, so the final bit should be
true, '????') AS TEAM
or
1=1,'????') AS TEAM


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cmmrfrds]

Thanks for the update Frederico, I couldn't remember the default part and the Help is not working on my work PC.

 

[uniroyal2001]

Hello Frederico,
I wish I could give you 5 stars.
It worked like a charm.
Thanks a million.