Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update within table (Access2003)

    Is this possible to update field in one table (where values are blank) to the another field in the same table?

    Region_____GeoRegion
    East
    West________WEST
    Central

    Update GeoRegion to Region where values are blank.

    Region_____GeoRegion
    East________East
    West________WEST
    Central_____Central

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update within table (Access2003)

    Create a query based on the table.
    Select Query | Update query.
    Add the Region and GeoRegion fields to the query grid.
    Enter the following in the Criteria line for the GeoRegion field: Is Null
    Enter the following in the Update to line for the GeoRegion field:

    [NameOfTable].[Region]

    where NameOfTable is the name of the table.
    Select Query | Run or click the Run button on the toolbar.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update within table (Access2003)

    WOW!
    My mistake was I was using
    Where IsNull([NameOfTable].[Region])

    Thanks so much!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update within table (Access2003)

    All great! Except now my all values are different case.
    Some all UPPER, some Normal with capital first letter.

    Is there way to make them all Normal? Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update within table (Access2003)

    The query will copy whatever is in the Region field. If you want it to use "proper" case, set the Update to line to

    StrConv([Region],3)

    This will only affect the values that are actually updated. It won't change the values in the GeoRegion field that have already been filled in. If you want to change those too, create another update query, and add only the GeoRegion field, with:

    Criteria: Is Not Null
    Update to: StrConv([GeoRegion],3)

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update within table (Access2003)

    Do you know EVERYTHING? :-))
    THANKS!

Posting Permissions

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