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

    Delete some words in the row (Access 2000)

    In my field address i want to delete the beginning words "seat" or "corespondence" for example in the line
    seat : 7200 Front Bench, deviation 0 i want to delete "seat:"
    How can i do it ?

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

    Re: Delete some words in the row (Access 2000)

    Create a query based on the table.
    Add the address field to the query grid.
    Enter the following expression in the Criteria line:
    <code>
    Like "seat: *"
    </code>
    Select Query | Update Query.
    Enter the following expression in the Update to line:
    <code>
    Mid([address],7)
    </code>
    (If you use the comma as decimal seprarator on your PC, you should use <code>;</code> instead of <code>,</code>)
    Select Query | Run to execute the query.

    If you need to do this in code:
    - Select View | SQL
    - Remove the line ends from the SQL string.
    - Copy it into your VBA code.
    - Replace the double quotes <code>"</code> with single quotes <code>'</code>
    - Place double quotes around the string.

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

    Re: Delete some words in the row (Access 2000)

    Thank you for your reply. Why i cannot update? It seems i have followed your lines, but it doesnt update.I am applying the example

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

    Re: Delete some words in the row (Access 2000)

    Because the address field in the single record in your table doesn't begin with <code>seat:</code> but with <code>seat :</code>
    If you want to update all records that start with the word <code>seat</code>, the condition should be
    <code>
    Like "seat*"</code>

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

    Re: Delete some words in the row (Access 2000)

    Thank you, it is fine now !. How shall i remove the second alternative, with one line, since there are two words available : seat or correspondence ?

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

    Re: Delete some words in the row (Access 2000)

    You'll have to create a separate query for each word you want to remove, since the number of characters is different each time.

Posting Permissions

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