Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Unanswered: Only update fields with null values - update query or VBA?

    I need to update a table with data from another table, but ONLY where the field is currently blank (Is Null). If the fields already have data, I don't want to over write it. I know that I can run an update query on one field at a time using IS NULL, but is there an easier way I can update all the fields (except the primary key) in the table without writing a separate update query for each field and running them together in a macro (there are a LOT of fields)? Maybe a query isn't the answer - maybe it can be done in VBA but I only know the very VBA basics. Any help would be greatly appreciated...


  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Update Table1
    Set Table1.field1=table2.field1
    From Table1 Inner Join Table2 Where Table1.field2=table2.field2
    Where Isnull(table1.field1)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Oct 2004
    Posts
    8
    Thanks for the post. I will give this a try (assuming I would still need to write out code for each of the field names in order to match them from table1 to table2) so only... 1 more question - where exactly would I set up the code to run? Don't mean to be so needy but there are infinite object properties to run VBA from and this looks a little like SQL (in which case I would run an SQL query?) but if VBA I'm just not sure what object/property location to run from.

  4. #4
    Join Date
    Oct 2004
    Posts
    8
    OK - I put my query in SQL and I see the idea but I am trying to state replace data in each field where it is null:
    WHERE (((Relationships.DlrNum) Is Null) AND ((Relationships.DlrLoc) Is Null) AND ((Relationships.County) Is Null) AND .... so on. (51 fields at once)
    and it is not updating anything because it is looking for All the fields to be null at the same time. Should I be replacing all those AND's with OR's?

  5. #5
    Join Date
    Oct 2004
    Posts
    8
    Unfortunately when I tried changing all the AND's to OR's it just replaced everything in every field (replacing good data with no data, which I did not want). I only want it to update the fields in table1 with table2 if table1 fields are blank, otherwise it should leave the good existing data in place. So that didn't work out for me in SQL.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I made a query once which put in: is null on each of the cascading criteria lines (thinking it to be an OR statement) but ending up updating fields I didn't want to. I ended up going with a separate update query for each field with the criteria as is null or = "".
    Last edited by pkstormy; 10-01-06 at 00:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2004
    Posts
    8
    Thanks for the posting. Using individual queries for each field is what I've done in the past but since I have 51 fields this time, I was looking for a way to avoid writing 51 different update queries. I was hoping someone might know of some VBA code that would manage the updates instead.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by TBBunn
    Thanks for the posting. Using individual queries for each field is what I've done in the past but since I have 51 fields this time, I was looking for a way to avoid writing 51 different update queries. I was hoping someone might know of some VBA code that would manage the updates instead.
    Well brute forcing in VBA isn't gonna be any faster ... Think, query for your records to update with. Query for the records to update on. You'll STILL have to evaluate/test each of the 51 fields for whether to update or not ...

    Something else you might wish to try: construct your 1st update query and duplicate for each of your remaining 50 fields. Then make those queries 1 by using a union ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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