Update Query empties the whole column of data

[DRANDON]

Hello all. I'm kind of new in Access Queries. I have a table of records where some fields have an "N/A". I want to replace "N/A" with just a blank cell. I created an Update Query
as follows:

Field: Rate Meth
Table: Tbl_to_Clean
Update to: IIf([Rate Meth]="N/A"," ")
Criteria:

Trouble is when I run the query it clears all the data in every record in that column whether there is an "N/A" or not. Can anyone help me? Thanks.

Oh, and i've tried "", "", and Null

 

[Joe4]

You need to put "N/A" on the Criteria line.
Then you just need " " on your Update To line (or Null, etc).

The Criteria line tells it which records to update.
The Update To line tells it what you want to update those records to.

 

[DRANDON]

It appears to be only updating those records where "N/A" shows up in every column stated in the query. (5 out of 1,614)

 

[Joe4]

Please switch your query to SQL View and Copy and Paste the code here so we can see exactly what it is telling it to do.

 

[DRANDON]

UPDATE [tbl_ALL_HOSP_Contact Matrix] SET [tbl_ALL_HOSP_Contact Matrix].[IP Rate Meth] = Null, [tbl_ALL_HOSP_Contact Matrix].[DRG Rate] = Null, [tbl_ALL_HOSP_Contact Matrix].[OP Rate Meth] = Null, [tbl_ALL_HOSP_Contact Matrix].[Medical/Surg/Peds] = Null, [tbl_ALL_HOSP_Contact Matrix].Surgical = Null, [tbl_ALL_HOSP_Contact Matrix].[ICU/CCU/PICU] = Null, [tbl_ALL_HOSP_Contact Matrix].PCU = Null, [tbl_ALL_HOSP_Contact Matrix].[OB C-Section] = Null, [tbl_ALL_HOSP_Contact Matrix].[OB NVD] = Null, [tbl_ALL_HOSP_Contact Matrix].[Boarder Baby] = Null, [tbl_ALL_HOSP_Contact Matrix].Rehab = Null, [tbl_ALL_HOSP_Contact Matrix].[Coronary Bypass] = Null, [tbl_ALL_HOSP_Contact Matrix].[Cardiac Valve] = Null, [tbl_ALL_HOSP_Contact Matrix].Angioplasty = Null, [tbl_ALL_HOSP_Contact Matrix].[IP CARD Cath] = Null, [tbl_ALL_HOSP_Contact Matrix].[Other Cardiac Svcs] = Null, [tbl_ALL_HOSP_Contact Matrix].[Psych Adult] = Null, [tbl_ALL_HOSP_Contact Matrix].[Psych Adolesc] = Null, [tbl_ALL_HOSP_Contact Matrix].[Psych OP] = Null, [tbl_ALL_HOSP_Contact Matrix].[Psych IOP] = Null, [tbl_ALL_HOSP_Contact Matrix].[2nd SL Threshold] = Null, [tbl_ALL_HOSP_Contact Matrix].[1st SL Threshold] = Null, [tbl_ALL_HOSP_Contact Matrix].[1st SL %] = Null, [tbl_ALL_HOSP_Contact Matrix].[1st SL Comments] = Null, [tbl_ALL_HOSP_Contact Matrix].[2nd SL %] = Null, [tbl_ALL_HOSP_Contact Matrix].[2nd SL Comments] = Null, [tbl_ALL_HOSP_Contact Matrix].[Stop Loss Calc] = Null, [tbl_ALL_HOSP_Contact Matrix].[OP Surg] = Null, [tbl_ALL_HOSP_Contact Matrix].[OP Serv] = Null, [tbl_ALL_HOSP_Contact Matrix].[ER Serv] = Null, [tbl_ALL_HOSP_Contact Matrix].[OP Lithotripsy] = Null, [tbl_ALL_HOSP_Contact Matrix].Observ = Null, [tbl_ALL_HOSP_Contact Matrix].[OP Cath] = Null
WHERE ((([tbl_ALL_HOSP_Contact Matrix].[IP Rate Meth])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[DRG Rate])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OP Rate Meth])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Medical/Surg/Peds])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].Surgical)="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[ICU/CCU/PICU])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].PCU)="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OB C-Section])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OB NVD])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Boarder Baby])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].Rehab)="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Coronary Bypass])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Cardiac Valve])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].Angioplasty)="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[IP CARD Cath])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Other Cardiac Svcs])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Psych Adult])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Psych Adolesc])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Psych OP])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Psych IOP])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[2nd SL Threshold])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[1st SL Threshold])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[1st SL %])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[1st SL Comments])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[2nd SL %])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[2nd SL Comments])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[Stop Loss Calc])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OP Surg])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OP Serv])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[ER Serv])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OP Lithotripsy])="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].Observ)="N/A") AND (([tbl_ALL_HOSP_Contact Matrix].[OP Cath])="N/A"));

 

[Joe4]

See all the "AND" statements in your WHERE clause? That means it will only update those records where ALL of those conditions are met. Hence, that is why it is only running on records where they ALL equal "N/A".

There is an important concept to understand when entering multiple Criteria items in Access. Everything entered on the same Criteria line will be treated as an "AND". If you enter each Criteria on a separate Criteria row, it will be treated as an "OR" statement.

Quite frankly, I never try to update more than one Condition (criteria) in an Update Query. If I had five different fields to check, I would probably set up 5 Update Queries, then create a Macro that run all five at once.

Could you do it all in one Update Query? Possibly, if you move each Criteria to its own Criteria line. You would have to try it out. However, I would strongly recommend backing up your table before trying it, in case it has the unintended affect of also deleting values you want to keep.

 

[DRANDON]

Thanks so much for your help on this. I understand what you are saying. I tried replacing all the ANDs to ORs but still had some data missing in the end. I agree to set up multiple queries to complete this task. On the other hand, I could just export the records to Excel and do a find and replace. What fun would that be..
Thanks again.

 

[Joe4]

Note that there is an easier way to do a whole table at once.
Open up the table, and hit CTRL-F to bring up the Find/Replace menu.
Go to the Replace tab and enter "N/A" in the Find What field, and leave the Replace With field blank.
Set the Look In field to your table name and then click Replace All.
That should replace all instances of N/A throughout your entire table at once.