CODEWITHSUNDEEP
sqlms-access
silverkid
silverkid

Reputation: 9573

how to do complete multiple replaces throughout table in ms-access

i am a little confused in finding out what would be the best way to replace all occurances of 1. Blanks 2. - 3. NA from all collumns of TableA with question mark ? charachter.

Sample Row in orignal tableA

444586  RAUR    <blank> 8   570 NA  -   13  -   SCHS299 MP  339 70  EN  <blank>

Same Row in Expected TableA

444586  RAUR    ? 8 570 ?   ?   13  ?   SCHS299 MP  339 70  EN  ?

please help me out

I cant use the Find Replace Toolbar of access.

Upvotes: 0

Views: 193

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166396

You can try something like this for all columns you require

UPDATE Table1 SET 
Table1.Col1 = IIf(Trim([COL1]) In ("","-","NA"),"?",[Col1]), 
Table1.Col2 = IIf(Trim([COL2]) In ("","-","NA"),"?",[Col2]), 
Table1.Col3 = IIf(Trim([COL3]) In ("","-","NA"),"?",[Col3]), 
Table1.Col4 = IIf(Trim([COL4]) In ("","-","NA"),"?",[Col4]), 
Table1.Col5 = IIf(Trim([COL5]) In ("","-","NA"),"?",[Col5]);

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

UPDATE tableA
SET myColumn = "?"
WHERE TRIM(myColumn) = "" OR TRIM(myColumn) = "-" OR TRIM(myColumn) = "NA"

Note: Take the backup of the table, before executing this statement.

Upvotes: 0

Related Questions