Default for switch function 1

[nithink]

Hi,

I'm using a switch function in a select statement,and the query is

SELECT ret_date as return_date,

switch([dbarc.cat_cd]="TOTA","TOTAL",
[dbarc.cat_cd]="TOTM","TOTM",
[dbarc.cat_cd]="EQT","EQTALL",
[dbarc.cat_cd]="FIX","FIXALL") AS class,'N'AS cash

FROM acct_category AS dbarc ;

The switch works fine,ie it changes,if it finds
the values'TOTA','TOTM','EQT','FIX' to the respective values

But if it finds other than these values I want to keep it
the same but what it does is puts a space in all other
values.So what should be added to preserve the other values
other than specified in the switch.

And can I pls know good websites for learning MS-Access SQL
Syntax ?

Thanks much
-GR

 

[Golom]

Switch" returns NULL if none of it's conditions are true. You may need to use something like
[tt]
NZ( switch([dbarc.cat_cd]="TOTA","TOTAL",
[dbarc.cat_cd]="TOTM","TOTM",
[dbarc.cat_cd]="EQT","EQTALL",
[dbarc.cat_cd]="FIX","FIXALL"),[dbarc.cat_cd]) AS class
[/tt]

 

[nithink]

Thanks Golom. It worked fine. Whats that NZ mean ? I'm very new to Access, my first assignment.

Can you pls tell me some links for good MS-Access stuff like, SQL Queries, syntax,etc...
-GR

 

[Golom]

NZ returns a default value (empty string for text data types and zero for numerics) if the first argument is NULL. It returns the value of the argument if it isn't NULL. You can include a second argument specifying what you want returned in the event that the first argument is NULL. For example NZ(textField, "NULL Field") will return the string "NULL Field" if "textField" is NULL.

 

[nithink]

Thanks Golom very much for the explanation.