A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Making null



 
 
Thread Tools Display Modes
  #1  
Old December 8th, 2009, 03:46 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old December 8th, 2009, 04:00 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default 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  
Old December 8th, 2009, 04:05 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old December 8th, 2009, 04:52 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.