Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Australia
    Posts
    26

    Unanswered: what's the best way to update multiple fields

    Hello!

    I want to do this [in access]:

    UPDATE table SET field1 = "this" WHERE field2 = "that" AND SET field1 = "thisotherthing" WHERE field2 = "thatotherthing"

    Before i was creating loops and opening and closing the connection all the time but it seems like a clumsy method.

    Regards
    RIM

    thanks in advance!

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    use

    UPDATE table SET field1 = DECODE(field2, 'that', 'this', 'thatotherthing', 'thisotherthing');

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company(
    http://www.alligatorsql.com

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    oh sorry ... I didn´t read that little word access. DECODE is for Oracle but perhaps you will find the same command in access ???

    Sorry again

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  4. #4
    Join Date
    Feb 2002
    Location
    North Carolina
    Posts
    13
    In the QBE grid, you can do the following:

    Set up an UPDATE query. Select field1 as the field to be updated. In the Criteria box, enter the following formula:

    IIf([field2] = "that","this","thatotherthing")

    If you need to test field2 for more than one value, use a nested IIf (see the Help file for assistance).

    If you want to do this in code, open a recordset based on the table. Assign field1 and field2 to variables, then use a SELECT CASE statement to test the value of field2 and update field1 accordingly.

    Hope it helps,

    j-Dog

Posting Permissions

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