Find Replace values when they are not null

[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.

 

[John Spencer]

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

 

[KARL DEWEY]

Try this --
UPDATE TableA SET TableA.Countycode = 0
WHERE (((TableA.Countycode) Is Not Null));

Allways BACKUP DATABASE before doing gobal stuff.

 

[teddyb777]

Thanks John. This did exactly what I needed.
Ted

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.

.