If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Making null
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! -- Milton Purdy ACCESS State of Arkansas |
#2
|
|||
|
|||
Making null
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! -- Daryl S "golfinray" wrote: 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! -- Milton Purdy ACCESS State of Arkansas |
#3
|
|||
|
|||
Making null
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! -- Milton Purdy ACCESS State of Arkansas "Daryl S" wrote: 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! -- Daryl S "golfinray" wrote: 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! -- Milton Purdy ACCESS State of Arkansas |
#4
|
|||
|
|||
Making null
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 golfinray wrote: 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! |
Thread Tools | |
Display Modes | |
|
|