Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Exclamation Unanswered: Find NULL and Replace with STATE

    Hi,

    This should be such an easy thing, but I'm pulling my hair out.

    I just received a large database with 500,000+ records. My client wants me to add a new field called "State" and populate each record with "RI"

    I've tried find and replace, but it won't accept an empty field

    It's too large to do by hand... surely there's a way to do this globally? (I'm no database expert, as you've probably guessed, but I tried to put in some SQL code in a Query... am I on the right track? )

    Thanks for any assistance!
    Janet

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    UPDATE yourTable
    SET State = "RI"

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Find NULL and Replace with STATE

    Try this (you must fix syntax for your situation)

    UPDATE TableName
    SET State='RI'
    FROM TableName
    WHERE State IS NULL
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  4. #4
    Join Date
    Apr 2004
    Posts
    2

    Re: Find NULL and Replace with STATE

    Okay, just a little clarification needed... do I do this as a part of an Update Query, or a module? Sorry, my "Access for Dummies" is falling short! Thanks


    Originally posted by SCIROCCO
    Try this (you must fix syntax for your situation)

    UPDATE TableName
    SET State='RI'
    FROM TableName
    WHERE State IS NULL

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can post that straight into a query and it will run just dandy.

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Find NULL and Replace with STATE

    It depends what you want to do exactly. If you are planning on running your query manually, put it into a query and run the query everytime you need to. If you want to do some automation for your upload then create the query and call the query from your code, or send the query through the code. The easiest way is the very first option.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

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