Find Replace values when they are not null

  • Thread starter Thread starter teddyb777
  • Start date Start date
T

teddyb777

I have a table that contains dollar amounts in random cells throughout the
table with the remainder of the cells containing nulls. I need to replace
every dollar amount with a zero while not interfering with the nulls. Can
someone help me with to create an update query that will accomplish this?
Thanks so much.
 
How many FIELDS are involved?

UPDATE SomeTable
SET FieldA = [FieldA] * 0
, FieldB = [FieldB] * 0

Null * 0 is Null
AnyNumberValue * 0 is Zero.

If you have only a few records that have values you might be better off doing
one a field at a time (multiple queries - one for each field involved) and
filtering out the records where the field is Null.

UPDATE SomeTable
SET FieldA = Null
WHERE FieldA is Not Null


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Try this --
UPDATE TableA SET TableA.Countycode = 0
WHERE (((TableA.Countycode) Is Not Null));

Allways BACKUP DATABASE before doing gobal stuff.
 
Thanks John. This did exactly what I needed.
Ted

John Spencer said:
How many FIELDS are involved?

UPDATE SomeTable
SET FieldA = [FieldA] * 0
, FieldB = [FieldB] * 0

Null * 0 is Null
AnyNumberValue * 0 is Zero.

If you have only a few records that have values you might be better off doing
one a field at a time (multiple queries - one for each field involved) and
filtering out the records where the field is Null.

UPDATE SomeTable
SET FieldA = Null
WHERE FieldA is Not Null


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a table that contains dollar amounts in random cells throughout the
table with the remainder of the cells containing nulls. I need to replace
every dollar amount with a zero while not interfering with the nulls. Can
someone help me with to create an update query that will accomplish this?
Thanks so much.
Click to expand...
.
Click to expand...
 
Back
Top