none
VBA to Find / Replace in all fields RRS feed

  • Question

  • Hello,

    I'm have a MS Access 2010 database on a Win7 PC. I receive a file from a SQL database that contains 93 fields which I import to a table named "CardsImport". Whenever a field has a null value, the word NULL appears. This occurs in all the fields.

    Currently I open the file in a text editor and perform a match case find NULL and replace with "".

    Is there a VBA that can inspect the entire table and find / replace the word NULL with a null value?

    Thanks for your help. Kevin
    • Edited by KevinATF Tuesday, November 13, 2018 3:30 PM added a ty
    Tuesday, November 13, 2018 3:29 PM

Answers

  • Thanks for your help. But I think I will just stay with opening the file in a text editor and perform a Find & Replace.

    Thanks again for your suggestions.

    Kevin

    • Marked as answer by KevinATF Tuesday, November 13, 2018 6:25 PM
    Tuesday, November 13, 2018 6:25 PM

All replies

  • Hi Kevin,

    There is but maybe it's easier to start over. What type of file did you get from SQL Server? How did you import it into Access? Are those fields supposed to have empty strings ("") instead of Nulls (not the string "NULL")?

    Tuesday, November 13, 2018 3:55 PM
  • Hi DBguy.

    It's a comma-delimited double quote text file. Currently I'm importing it manually into Access. In the application the SQL server supports, the fields are blank that contain the word NULL in the file I receive. I'm not sure, but I think where ever no information is entered into the application, the SQL table inserts the word NULL for any empty field when exporting the file.

    Here's an example of the text file I receive after importing it into Access. Hope this helps. Thanks.

    Tuesday, November 13, 2018 4:08 PM
  • I see. Then you don't want to use "" to replace the "NULL" values. You probably want to use Null instead.

    One possible approach is you could create a form and add a multi-select listbox to show all the fields in the table. You can then select all the fields you want to update and then loop through the selected items executing the following SQL statement.

    strSQL = "UPDATE TableName 
    SET [" & Me.ListboxName.ItemData(x) & "] = Null
    WHERE [" & Me.ListboxName.ItemData(x) & "] = 'NULL'"

    Hope it helps...



    • Edited by .theDBguy Tuesday, November 13, 2018 4:36 PM
    Tuesday, November 13, 2018 4:35 PM
  • That sounds like a good idea. However, I have over 90 fields. Is it possible to add a "select all" option to the list box?

    Thanks.

    Tuesday, November 13, 2018 4:41 PM
  • That sounds like a good idea. However, I have over 90 fields. Is it possible to add a "select all" option to the list box?

    Thanks.

    Of course. Loop through the Listbox using the ListCount property and set the Selected or ListIndex property to select it.

    Hope it helps...

    Tuesday, November 13, 2018 5:27 PM
  • Thanks for your help. But I think I will just stay with opening the file in a text editor and perform a Find & Replace.

    Thanks again for your suggestions.

    Kevin

    • Marked as answer by KevinATF Tuesday, November 13, 2018 6:25 PM
    Tuesday, November 13, 2018 6:25 PM
  • Thanks for your help. But I think I will just stay with opening the file in a text editor and perform a Find & Replace.

    Thanks again for your suggestions.

    Kevin

    Hi Kevin,

    Good luck. Just remember, there is a difference between a Null and an empty string, in case it matters later on.

    Cheers!

    Tuesday, November 13, 2018 6:43 PM
  • Is there a VBA that can inspect the entire table and find / replace the word NULL with a null value?

    Hi Kevin,

    It is very easy to make your own import-routine, especially for csv-files. This gives you the possibility to manage the data in the way you want, and not what MS default does for you.

    You can open the csv-file, and read the file line by line.

    Each line can be split using the Split function in the individual cell values using the comma or semicolon as separator.

    Then you can assign the array value to the successive fields of a recordset. This is all standard.

    Your specific part: Each time the array value has a "Null" value, you can assign a NULL value to that field.

    In this way you can design almost any very specific import routine.

    Imb.

    Tuesday, November 13, 2018 10:36 PM