Making null

[golfinray]

In the number field of a table, I have some numbers and some nulls. I would
like the entire field to be null. NOT zeros. Just blank. Do I need a delete
query or what in order to do that? Thanks so much for the help!

 

[Daryl S]

Golfinray -

Before messing with data, make a backup...

To change existing data, you need an update query. You will add only the
number field you are trying to change to the design grid. In the Criteria
row, enter a zero (0). In the Update To row, enter Null. Switch to
datasheet mode, and you should only see the zeros (this is showing the
current value of the records that will be changed). Switch back to design
mode and run the query (red exclamation point button).

You may want to fix the source of the zeros if you can. If your table shows
a default value of zero for that field, you may want to remove that default
value. If your data comes from a form, then you can change zeros to nulls
with a BeforeUpdate event on that numeric field. If it is imported data,
then you may need to run the query each time you import.

Hope that helps!

 

[golfinray]

Users have mistakenly entered data in a field where we did want them to enter
any data. We just want to set the field back to blank or null, not zero.
Thanks!

 

[John Spencer]

If you want to do this for every value in the field then

UPDATE YourTable
SET YourField = Null

In query design view
== Add the table
== Add the field to the field list
== Select Query: UPdate from the menu
== In the UPDATE TO under the field enter
Null
== Select Query: Run from the menu

As usual consider backing up the data BEFORE you run the query. You cannot
recover from this without a backup.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County