change a zero's to null in a table ?

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 


0
Haggis
1/25/2008 12:23:00 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1066 Views

Similar Articles

[PageSpeed] 37

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
'====================================================


Haggis wrote:
> 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 
> 
> 
0
John
1/25/2008 12:47:39 AM
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

"Haggis" <zeddySPAM@MEeastlink.ca> wrote in message 
news:OYwfzhuXIHA.5472@TK2MSFTNGP06.phx.gbl...
> 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
> 


0
Jeanette
1/25/2008 12:49:06 AM
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" <zeddySPAM@MEeastlink.ca> wrote in message 
news:OYwfzhuXIHA.5472@TK2MSFTNGP06.phx.gbl...
> 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
> 


0
Jeanette
1/25/2008 12:53:13 AM
thanks to everyone that responded...

I am where i want to be <g>

Cheers!
David
"Jeanette Cunningham" <nnn@discussions.microsoft.com> wrote in message 
news:eP9yxyuXIHA.5160@TK2MSFTNGP05.phx.gbl...
> 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" <zeddySPAM@MEeastlink.ca> wrote in message 
> news:OYwfzhuXIHA.5472@TK2MSFTNGP06.phx.gbl...
>> 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
>>
>
> 


0
Haggis
1/25/2008 4:39:44 PM
Reply:

Similar Artilces:

changing report headings names
I would like to give the user the option of changing the report heading names, I can do this with the Docmd.openreport, stdocname, acviewdesign,,,achidden and then grab the current heading and display that heading and allow the user to change it. I then do a docmd.close acreport, stdocname, acSaveYes, this works just fine when I test but when I compile the program into a mde it doesn't like the it. Is there another way of doing this? Thanks for any help. Tom you can write the current heading names to a table, making sure the table holds only one record. add the table to the quer...

Change of public IP for fighting spam
Hi, If I ask my dns registor to change my ip to another public ip. Is this going to help us fight spam? Is there's a risk of not getting email? Example old: www.company.com = 60.1.1.1 Examble new: www.company.com = 60.1.1.5 By the way, I noticed that most big companies cannot be ping anymore. Is this advisable too? Thanks, Ricky On Thu, 20 Apr 2006 08:06:02 -0700, RickyVene <RickyVene@discussions.microsoft.com> wrote: >Hi, > >If I ask my dns registor to change my ip to another public ip. Is this >going to help us fight spam? Is there's a ris...

How do I change a flyer (word document) into a picture?
I created a flyer and I want to change it into a picture so I can upload it as a picture on facebook. How do I change the 2007 word document into a picture? This newsgroup is for Microsoft Project, a planning and scheduling application. Questions about Microsoft Word, should be published to the Word newsgroup. To address your question, try using File/Save As... and select type PDF. If the document must be an image format (such as BMP, JPG, GIG, etc) you will probably need some screen capture software such as Snag-It or the clipping tool available with Windows Vista and later. ...

A bug in GUI of creating query with more outer joins between two tables
Hello, in GUI of Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6521.5000) is generally very simple to make a select query between two tables joined with two or more outer joins (of the same kind normally). But you should be careful. If you build first join clicking on a field of the first table, drag then the connection to the field of the second table and choose the right outer join, then you have to make the next outer join between the same two tables on the same way. If you begin but with the second table first and although you choose the same type of outer join (the arrow...

leading zero problem
Hi, I have been given some call data from my telecomms provider in csv format. I have tried importing that into excel so I can then import into an access table but the format got messed up so I have converted the raw data file to txt. Problem with both when I import into excel is that in my CalledNo cell, I have a leading space in front of the phone number which is of the format 01234567890. I have tried using the data > text to columns tool but this deletes the 0 even tho I keep the cell format as text. I have also tried TRIM function however not all numbers are the same length. Thanks ...

Hyperlink Changed
The local power company had a problem last week. The power nearly went off and came back rapidly several times. I have a spreadsheet with a lot of hyperlinks to other files. After the power fluctuations the hyperlinks have the wrong path names to the files. The file names are all correct, but the path to them are all wrong. Is there a way to restore the proper path name in mass, or must I correct each and every one? The wrong path names are all the same. Does anyone know how this could have happened in the first place? ...

why cant i change font
i select all the text in a textbox and try to apply a font. nothing happens. that's the case with 4/5 fonts, only a few work. the same appears in both publisher 2003 and 2007. Are you creating a web page? Publisher will show 11 or so fonts when you are creating web pages. To use all the fonts, on the toolbar, Format, Font, uncheck Show only Web fonts. If above is not the issue, are you getting an error? -- Mary Sauer MVP http://msauer.mvps.org/ "ayudameconpublisher" <ayudameconpublisher@discussions.microsoft.com> wrote in message news:1BE1262A-A70B-4...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Best way to change style of a table
I have a table I added via VBA like this: set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTable(...) I then add some cells to it by: oTable.Cell(...).Shape.TextFrame.TextRange.Text = "some text" However, I cannot get a handle on these cells to update the font, fill, etc. What is the best way to do this? On Sun, 13 Dec 2009 21:01:01 -0500, Clifton Ivey <clifton ivey <"africom.mil>"> wrote: > I have a table I added via VBA like this: > > set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTa...

"change order form"
Does anyone have a change order template that I can review? ...

changing from outlook 2000 to outlook xp
i just bought a new computer and want to move all my outlook 2000 personal folders from my old computer--to my new computer which is running outlook xp. What's the best way to do this? thanks, Sammy http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer sammy russo <sammy@aorafting.com> asked: | i just bought a new computer and want to move all my | outlook 2000 personal folders from my old computer--to my | new computer which is runnin...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

Changing margins on different pages in document templates.
I was wondering how you change the margins for different pages in a template. Specifically, my firm is using a letterhead for the first page but blank pages after that so the header and footer margins on the first page ONLY must be larger than normal, while the following pages are fine with default settings for the margins. How do you do this and save it as a template? See http://sbarnhill.mvps.org/WordFAQs/Letterhead.htm, especially the "More complex letterhead" section. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://wo...

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

How to change Marketing list member "All members" to "Active membe
How to change Marketing list member "All members" to "Active members" in CRM 4.0? Any help is appreciated. I have used the following code giving object required error code: /************************************************************** * Change the default view of the Marketing List Members (Account) **************************************************************/ if (crmForm.all.createdfromcode.DataValue == 1) { if (crmForm.FormType != 1 && crmForm.FormType != 5 && crmForm.FormType != 6) { /************************************************************** ...

Criteria to return all records if selection from form is null
Hi, I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*") And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. Quite understandable since Like "*" doesn't return Nulls. What I'm trying to do is to return all records when there is nothing chosen in the selection box on the form. I've tried a bunch of different IIfs wi...

word doc lost all changes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I made lot's of changes to a word doc on Friday, after every few minutes I saved the document. Now that I have opened it again today, it seems to be in it's original format with absolutely no changes. <br><br>Is it possible I could have deleted the saved document rather than the original - but i renamed it with a different version! Can I back up the Mac to Friday evening so that both docs will be there? <br><br>Any other suggestions?? <br><br>Help - please! Hi Dawn...

How do I change reply to or senders address
How do I change the Reply to: email address or the sender’s address? My infrastructure consists of: 1 – Windows 2003 AD box 1 – Exchange 2003 box Many – Outlook 2003 email clients. Is there a way to change the senders email address, example; An email note that’s generated by joe in the domain test.foo.com (joe@test.foo.com) can this address be changed in some setting in either Exchange or Outlook, so that the recipient of the note sees the senders address as joe@foo.com? The Outlook clients are configured for an Exchange connection. On Tue, 1 Mar 2005 11:03:46 -0800, HMR <HMR@discu...

rule
There is a nice rule at outlook express so that messages from certain poeple are in different colors. Why there is not such rule at Outlook 2003? There is and it's even better! See; http://www.howto-outlook.com/howto/coloremail.htm and http://www.howto-outlook.com/howto/coloremailadvanced.htm -- Roady [MVP] www.howto-outlook.com Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD slipstreamed with Service Pack 3 ----- "constantinos" <anonymous@discussions.microsoft.com> wrote in message news:5B1EAC31-909A-4088-97EB-BD6A97A71EC0@microsoft.com.....

Change what populates subject line when send pdf report in email
When I choose to send my POP Purchase Order Blank Form to a mail recipient (pdf), information is populating the subject line and body of my e-mail by default. Specifically the name of the report is populating the subject line. Is there a way to change this? I would rather see the PO number populating that field. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggest...

Users accessing same table
I have an Access 2003 database on a small network (server + 4 pc's) with the database split and each PC having its own front end. Recently the users have reported not being able to access the same form at the same time, they have to wait until the other user has finished and closed it before accessing it. Any suggestions regarding this problem? What error message are they getting? They should have no issue opening the same form, but there could be a lock conflict if they try to access the same record at the same time. Please post some more information (the exact error) s...

Invalid partition table
I have a Toshiba Laptop which came pre-installed with Vista home. Lately, I have received an Invalid Partition Table on bootup. I tried using the recovery disk with the OS on it as a boot disk but it doesn't seem to be working even when I tell the bios to boot from CD/DVD. Any suggestions on how I can create a boot disk and have the computer "see" it upon startup? Thanks so much!! Sandy -- regnells Posted via http://www.vistaheads.com If it is still under warrantee, take it back to the store for a refund or another that works. "regnells&quo...

Anchor clipart in a powerpoint table
How do I anchor clip art or inserted pictures into a cell in a Powerpoint table. We have different team logos 'on' a table to show roles and responsibility, I need to be able to get the logos 'in' the cells so they move relative to the table as it is edited. Regards There's no simple way to do this. There's a possible work around here: http://www.pptalchemy.co.uk/PowerPoint_Tables_Picture.html -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "iwaddo&...

Pivot Table
I have a list in a file name abc.xls. I have defined the list with a name say XYZ. I have another file Summary.xls with a pivot table that I'm using to give me a summary of that list. The 'XYZ' which is the defined name of the actual list in abc.xls file works when both the files are open. When I open the Summary.xls file, first message says "Reference is not valid" and second message says "Cannot find "abc.xls file". If I first open the abc.xls file and then the summary.xls, then it works and not viceversa. Please update. Thx. As answered in microsof...

Outlooks "Incoming mail (POP3)" keeps changing to "local"
Hi everyone, I have a question on Outlook 2000, every few days my account setting for the "Incoming mail (POP3)" change from the mail server I have specified to "local" and the mailServer then gets added to the "Account name" /meMailServerName Any ideas on why is it doing this, and can I stop it from doing this, cause each time it does, I have to manually change it back to original setting so I can get my mail. Shawn This is your antivirus software, most likely (or antispam software). Outlook doesn't do this. Check with the mfr. to see if they have an...