Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update a row (Access 2000)

    Is it possible to copy the last line in the field address, beginning with "deviation " and to update with it another field field called Trends ? I am writing only one row in order to be more compact, but there are a lot of rows.

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

    Re: Update a row (Access 2000)

    Create a query based on the table,
    Add the trends field.
    Select Query | Update Query.
    Enter the following expression in the Update to line:

    Mid([address],InStr([address],",")+2)

    (If you use the comma as decimal separator, use ; instead of , in the expression)
    Select Query | Run to execute it.
    The SQL for the query is
    <code>
    UPDATE Gardens SET trends = Mid([address],InStr([address],",")+2)</code>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a row (Access 2000)

    Thanks it is fabulous.Why cant i delete then this part after the update? The line with delete just didnt work:
    DELETE Gardens SET trends = Mid([address],InStr([address],",")+2)

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

    Re: Update a row (Access 2000)

    A delete query is intended to delete entire records, not parts of fields, and it has a different syntax than an update query, so your SQL doesn't make sense. If you want to remove the first part from the address field, use
    <code>
    UPDATE Gardens SET address = Left([address],InStr([address],",")-1), trends = Mid([address],InStr([address],",")+2)
    </code>
    Warning: you can run this query only once, so test it on a copy of the database.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a row (Access 2000)

    Thank you for the advice.I used it but i got the message ' syntax error.missing operator" Maybe i got the brackets wrong ?

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

    Re: Update a row (Access 2000)

    The database you attached contains a different query.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a row (Access 2000)

    Sorry.Please forget the query, i already succeeded to convert your line into a code and it works great ! , so a can populate now the field trends. I need now , after i have updated the field trends,to delete the words starting with "deviation 0" in the field address.And i get the syntax error

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

    Re: Update a row (Access 2000)

    You haven't modified the SQL correctly: the original was
    <code>
    UPDATE Gardens SET address = Left([address],InStr([address],",")-1), trends = Mid([address],InStr([address],",")+2)
    </code>
    and your code has
    <code>
    StrSQL = " UPDATE Gardens SET address = Left([address],InStr([address],',')-1), trends = Mid([address],InStr([address],,,')+2)"
    </code>
    You have replaced one of the double quotes near the end with a comma <code>,</code> instead of with a single quote <code>'</code>.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a row (Access 2000)

    Just to let you know that it works great ! manty 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
  •