change a zero's to null in a table ?

[Haggis]

Hello all,

without using a search and replace utility , is there a method to remove or
change any field that contains a '0' to null ?


TIA
David

 

[John Spencer]

Do you want to change the value to null if the value is zero?

Or do you want to change the value to null if the value has a zero
anywhere in it?

I am guessing the former. You should be able to use the following
expression if you just want to do this temporarily

IIF([SomeField] = 0, Null, SomeField)

If you want to do it permanently, you would probably use an update query.

UPDATE SomeTable
Set SomeField = Null
WHERE SomeField = 0



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeanette Cunningham]

David,
Here is an example for a number field of type Long Integer

UPDATE tblA SET tblA.ANbr = Null
WHERE (((tblA.ANbr)=0));

The table is called tblA
The field is called ANbr

Jeanette Cunningham

 

[Jeanette Cunningham]

David,
Here is an example for a text field.

UPDATE tblA SET tblA.ALtr = Null
WHERE (((tblA.ALtr)="0"));

To do this in query design
--drag your table to the query
--select the field with 0
--in the criteria row enter 0 for a number field or "0" for a text field
--on the menu >> Query >> Update query
--in the Update To row type Null
--run the query

Jeanette Cunningham

 

[Haggis]

thanks to everyone that responded...

I am where i want to be <g>

Cheers!
David