SQL Case Statement Logic

[morriskc]

Is there any way to use the SQL case statement logic to create a column in Access? I am using Access 2000 & the following is the statemnt I am trying to add as a column.

CASE WHEN (T1."ProductClass" IN ('5A','5B','5C','5D','5E','5F','5G','5H','5J','5K','5L','5M','5N','5P','5R','5T','5U','5V')) THEN ('HARDENERS')
WHEN (T1."ProductClass" IN ('5W','5X','5Y','5Z','50')) THEN ('RESINS') WHEN (T1."ProductClass" IN ('51','52','53','59')) THEN ('DILUENTS')
WHEN (T1."ProductClass" = '54') THEN ('CUSTOM')
WHEN (T1."ProductClass" = '57') THEN ('OILS') ELSE ('OTHER') END

Any help is appreciated.

 

[ejc00]

morriskc,

i'm not entirely clear on what you're trying to do... do you wish to create one column with the values "hardeners", "resins", etc. as the values? if so, then you can do this using an "Update" query... another option is to use an INSERT statement where you insert the column into the table... and your values would be part of a nested IIf statement.

 

[dhookom]

I would create a table that has unique values of ProductClass. Then add a field for ProductType where the values would be HARDENERS, DILUENTS, CUSTOM, OILS, and OTHER. This allows you to maintain all of this with data in queries rather than long-winded expressions that are hard to manage.

Duane
MS Access MVP

 

[cmmrfrds]

The counterparts of the case statement in Access are either the IIF or Switch Functions, but I am not sure you can use a list in the functions. Look up the 2 functions in Help.


iif(T1."ProductClass" = '54','custom',False part)

SWITCH(T1."ProductClass" = '51','DILUENTS',T1."ProductClass" = '52','DILUENTS',T1."ProductClass" = '53','DILUENTS',T1."ProductClass" = '59','DILUENTS')

 

[dhookom]

You could use something like
IIf(Instr("~5A~5B~...", "~"&[ProductClass] & "~&quot>0,"HARDENERS", ...))))))))

Duane
MS Access MVP

 

[morriskc]

Thanks all,

I did as Duane suggested and added a table to the database. The table approach worked great!