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.

 

[PieterLinden via AccessMonster.com]

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.

oookay. You do realize that Null dollar amount means something different
than Zero dollar amount. Null means unknown. Zero does not. But if you're
sure you want to do that, use NZ()

UPDATE SpreadsheetTable SET SpreadsheetTable.A = Nz([A],0)
WHERE (((SpreadsheetTable.A) Is Null));

 

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

.