Case Statement

[kumark]

Hi all i am not able to run this query in the Form i am getting run time error
complex query like that

my query is



"Update UOM SET VALID = SWITCH(UOM LIKE '*Bag*','BG',UOM LIKE '*Basket*','EA',UOM LIKE '*Bottle*','BT',UOM LIKE '*Box*','BX',UOM LIKE '*Carton*','CN',UOM LIKE '*Case*','CA',UOM LIKE '*Centiliter*','EA',UOM LIKE '*Day*','DAY',UOM LIKE '*Dispenser*','EA',UOM LIKE '*Dozen*','DZ',UOM LIKE '*Drum*','EA',UOM LIKE '*Each*','EA',UOM LIKE '*Feet*','FT',UOM LIKE '*Gallon*','GL',UOM LIKE '*Gram*','EA',UOM LIKE '*GramS*','EA',UOM LIKE '*Gross*','EA',UOM LIKE '*Hour*','HR',UOM LIKE '*JOB*','JB',UOM LIKE '*Kilogram*','KG',UOM LIKE '*Kit*','KT',UOM LIKE '*Liter*','EA',UOM LIKE '*Lot*','LT',UOM LIKE '*Month*','MN',UOM LIKE '*Package*','PK',UOM LIKE '*PACKET*','PK',UOM LIKE '*Pad*','PD',UOM LIKE '*Pair*','PR',UOM LIKE '*PIECE*','PC',UOM LIKE '*Roll*','RL',UOM LIKE '*SERVICE*','EA',UOM LIKE '*Set*','ST',UOM LIKE '*SHEET*','SH',UOM LIKE '*SPINDLE*','EA',UOM LIKE '*Tube*','TB',UOM LIKE '*Vial*','VL',UOM LIKE '*Year*','YR', TRUE, 'EA');"


please help

 

[llkhoutx]

What a mess. I'd need a guide dog to get me through your query. I function in your query would be easier to understand and get the result you want.

 

[kumark]

Now u don't need a guide dog to read



"Update UOM SET VALID =
SWITCH
(
UOM LIKE '*Bag*','BG',
UOM LIKE '*Basket*','EA',
UOM LIKE '*Bottle*','BT',
UOM LIKE '*Box*','BX',
UOM LIKE '*Carton*','CN',
UOM LIKE '*Case*','CA',
UOM LIKE '*Centiliter*','EA',
UOM LIKE '*Day*','DAY',
UOM LIKE '*Dispenser*','EA',
UOM LIKE '*Dozen*','DZ',
UOM LIKE '*Drum*','EA',
UOM LIKE '*Each*','EA',
UOM LIKE '*Feet*','FT',
UOM LIKE '*Gallon*','GL',
UOM LIKE '*Gram*','EA',
UOM LIKE '*GramS*','EA',
UOM LIKE '*Gross*','EA',
UOM LIKE '*Hour*','HR',
UOM LIKE '*JOB*','JB',
UOM LIKE '*Kilogram*','KG',
UOM LIKE '*Kit*','KT',
UOM LIKE '*Liter*','EA',
UOM LIKE '*Lot*','LT',
UOM LIKE '*Month*','MN',
UOM LIKE '*Package*','PK',
UOM LIKE '*PACKET*','PK',.
UOM LIKE '*Pad*','PD',
UOM LIKE '*Pair*','PR',
UOM LIKE '*PIECE*','PC',
UOM LIKE '*Roll*','RL',
UOM LIKE '*SERVICE*','EA',
UOM LIKE '*Set*','ST',
UOM LIKE '*SHEET*','SH',
UOM LIKE '*SPINDLE*','EA',
UOM LIKE '*Tube*','TB',
UOM LIKE '*Vial*','VL',
UOM LIKE '*Year*','YR',
TRUE, 'EA');"