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

Question

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.

Answer 1

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.

Answer 2

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]);

Answer 3

Here's hoping you may draw inspiration from this article:

How To Handle Missing Information Without Using (some magic value)