Case Staement

[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

 

[vbaInet]

kumark,

It looks too complex and hard too read.

* Create a table that will hold the UOM value and the replacement value. Something like this:

UOMValue   |   Replacement Value
Bag            BG
Basket         EA
Bottle         BT

... etc
* Join this table to your UOM table and change the JOIN to look like this:

FROM UOM INNER JOIN NewTable ON UOM.VALID LIKE "*" & NewTable.UOMValue & "*";

That's it.

But anyway, your table is poorly built. You need to normalise. In the real world, the replacement value is what you should be using to map to the UOMValue and not the other way round.

 

[kumark]

Now it might be easy 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');"

 

[kumark]

The problem is that i am automating it in a click of a button in the Form.so i need this query ..is there any other way to write it. because there are 1000's of records to be updated to the valid uom so only.

 

[vbaInet]

Write the values to a temporary table and use that table.