Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nz Question. (2000 (SR-1))

    I have a large table with 300 rows and 40 columns.

    Is there a way to format an update query to change all nulls in all columns to zero?

    OR

    How can I change all the nulls to zero?
    There is always a way.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Nz Question. (2000 (SR-1))

    Hi,
    You can run an update query where you update each field to:
    Nz([fieldnamehere],0)
    I assume all your fields are numeric? Otherwise you might want to choose something else (e.g. "") to update text fields to.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz Question. (2000 (SR-1))

    In the criteria line of each column, put IS NULL; in the 'update to' line of each column, put 0 (Zero). Copy your table first, then try this.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nz Question. (2000 (SR-1))

    Be VERY careful. Rory's solution is the only safe one that will selectively update all the null fields in a single pass without tripping over the internal limits on OR levels in a query. However, if you have a lot of nulls, don't expect it to be fast.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •