Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Update Query Criteria


    I have a relatively simple, single table database containing 300 records that needs some updating.

    There are ten text fields in each record. Any one of those fields may contain the string that I want to change. I staggered the criteria for the update query so Access combines them with the OR operator. The sql view is as follows:-

    UPDATE Customers SET Customers.firstname1 = "JOE", Customers.firstname2 = "JOE", Customers.firstname3 = "JOE", Customers.firstname4 = "JOE", Customers.firstname5 = "JOE", Customers.firstname6 = "JOE", Customers.firstname7 = "JOE", Customers.firstname8 = "JOE", Customers.firstname9 = "JOE", Customers.firstname10 = "JOE"
    WHERE (((Customers.firstname1)="FRED") OR ((Customers.firstname2)="FRED") OR ((Customers.firstname3)="FRED") OR ((Customers.firstname4)="FRED") OR ((Customers.firstname5)="FRED") OR ((Customers.firstname6)="FRED") OR ((Customers.firstname7)="FRED") OR ((Customers.firstname8)="FRED") OR ((Customers.firstname9)="FRED") OR ((Customers.firstname10)="FRED"));

    I'm concerned that if executed, this statement would fill every firstname field with "JOE" in any record if it finds only one instance of "FRED".

    Ideally I only want the query to update fields that contain "FRED" and not fill every FirstName field with "JOE" regardless.

    Any clues?


  2. #2
    Join Date
    Jun 2003

    don't execute the query! It would enter indeed everywhere "JOE" and you don't want that to happen!

    Simple solution : open the table itself and use the command <Edit><Replace> in the menu bar. Make sure the 'Look In' value is set to the table, so every instance of "FRED" will be replaced by "JOE" and only in those fields where it appears.

Posting Permissions

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