Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an Excel file that originated in Oracle to which I am linked in Access. I have a make table query that creates the table "Cases" that I am using for reports. I would like to remove all instances of the text "(null)" amd replace it with null. I can do it manually from the open Cases table by selecting Edit, Replace from the menubar, typing (Null) in the Find What: box, leaving the Replace With: box empty, and selecting Cases : Table in the Look In: box, and then clicking the command button Replace All. Can this be done in code or in a single query that can be carried out with the click of a command button?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a query based on the table.
    Select Query | Update Query.
    Add the relevant field to the query grid.
    Enter "(null)" in the first Criteria line.
    Enter Null (without quotes) in the Update to line.
    Click the Run button or select Query | Run.

    The VBA equivalent for this is

    CurrentDb.Execute "UPDATE TableName SET FieldName=Null WHERE FieldName='(null)'"

    (you must substitute the correct name of the table and of the field, of course)

    If you have multiple fields in which you want to replace "(null)", you'll have to create a separate update query for each field, or create a VBA instruction for each field.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have 21 fields that need to be updated so I was trying to avoid a separate query for each one. I used code and it worked very quickly.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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