Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    41

    Unanswered: Replace all instances of a value in a table

    I have a table that has over 100 fields with all different types of data. I am looking for a way to change every field that contains a data value of 999 to a zero. Is this possible without having to build multiple update queiries?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lynchjl7748
    Is this possible without having to build multiple update queiries?
    no, it isn't, you'll need one query per column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    From an MS Access Point of view, you could probably do this in VBA.

    Are they normal MS Access Tables in an MDB file?

    What version of Access have you got?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Feb 2005
    Posts
    41
    Yes, they are just basic access tables housed in an Mdb file. Currently I am working with access 2003. I was thinking VBA but I am far for proficient at programing.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    OK - Can you just clarify a few things;

    Its just one table we are talking about?
    (Not actually a problem but pls confirm)

    Are there any other fields in the table which may contain 999 pertaining to a foreign key from another table for example - you have to be absolutely certain?

    What datatype are the fields in question (or does EVERY field have to be considered)?

    If you can clarify the above, I will post you some code.

    To be honest, I have my suspicions that there are other issues with your db given what you are trying to do - can you give us an insight as to why you want / have to do this?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Search and Replace might do the trick. No queries needed.
    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

  7. #7
    Join Date
    Feb 2005
    Posts
    41
    the 999 value needs to be replaced in one table. The reason we have the data is an ETL programming is loading it due to a blank in the source document. I am working with the developer to get it fixed but due to competeing priorities it isn't at the top of his list. None of the key values that are being used contain that string of 999. The data types of the fields are text and currency.

    As stated above the issue isn't with the actual database but more with the process of migrating data from an excel workbook into a record we can load in to Access.

    Thanks for all your help.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Given it's only one table, Have you tried StarTrekkers excellent suggestion?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just back it up first!
    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

Posting Permissions

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