Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Find and Replace in Query Builder

    How do I "find and replace" throughout an entire table in Access? I need to search for the word "NULL" in every field in a table and replace it with "". There about 30 columns in this table and I'd like to do a "global" find and replace without using 30 "If" statements.

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can I ask why?

    For clarity, you're not really searching for the word "Null"... you simply want to replace all NULLS with a zero length string.

    Are all 30 columns text or are there dates and numbers too?

    Of those that are text, do they all have the Allow Zero Length property set to Yes?

    ATM I can't think of an easy way to do this... but I'll think on it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2008
    Posts
    3

    Find and Replace

    Thanks for the reply. The reason I need to do this is this table is being loaded (on a recurring basis) into a much larger database table. Fields with "NULL" cause the load to break. This only happens in numeric fields. So I can do a find replace "NULL" with "0". That would be fine.

    Is there any global find and replace function in query builder? If not, I can isolate the 15 or so fields that could potentially contain "NULL" and use the UPDATE function to search each column seperately. I was hoping there was an easier way to do this.

    Thanks again!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    You don't want to find and replace, you want to UPDATE the table.
    Code:
    UPDATE my_table
    SET    some_field = ''
    WHERE  some_field IS NULL
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2008
    Posts
    3

    Find and Replace

    Is there a way to do that across the entire table or so I need to do this one column at a time?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    I think Nz() is the right function here..
    Code:
    UPDATE my_table
    SET    col1 = Nz(col1, '')
         , col2 = Nz(col2, '')
         , col3 = Nz(col3, '')
    ...
         , colN = Nz(colN, '')
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,518
    sounds to me like the destination table isn't NULL aware, a zero isn't the same as a null value

    whats the destination table setup/column defintion, whats the difference betweent he table which allows NULL and the one that doesn't

    is it possible you are trying to send a test value of "NULL", rather than NULL
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cblevin0822
    Fields with "NULL" cause the load to break. This only happens in numeric fields.
    omigod, why would you want to set a numeric column to an empty string???

    why not set it to NULL instead?

    plz note, there is a huge difference between NULL and 'NULL'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by cblevin0822
    The reason I need to do this is this table is being loaded (on a recurring basis) into a much larger database table. Fields with "NULL" cause the load to break.
    My first thought on that would be: can you fix the "load"?

    Is there any global find and replace function in query builder?
    No.

    I can isolate the 15 or so fields that could potentially contain "NULL" and use the UPDATE function to search each column seperately.
    Only 15 now?! ^.^ I think this is your best course of action tbh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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