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  dBforums > PC based Database Applications > Microsoft Access > Find and Replace in Query Builder

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,002
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,002
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
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,082
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #9 (permalink)  
Old
L33t Helpa Munky
 
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"?

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

Quote:
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On