Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search and Replace (Access 2000)

    Hello: I have what is probably an elementary question (from an elementary user) for most of you. I have a data base with a series of numbers 56xxxxx and a series of numbers 57xxxxx in one of my fields. Now, I have to change all of the numbers beginning with 56 to begin with 57 and all of the numbers beginning with 57 to 56. Is there an easy way to do this, or do I just do it manually? Thanks, Sid

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

    Re: Search and Replace (Access 2000)

    You can use a query for this:
    - Create a query based on your table.
    - Add the number field to the query grid.
    - Set the Criteria for this column to <code>Like "5[67]*"</code>
    - Switch to datasheet view to check that the records to be changed are selected.
    - Switch back to design view.
    - Select Query | Update Query.
    - Enter the following expression in the Update To line and replace NumberField with the name of your field
    <code>
    Val((113-Left([NumberField],2)) & Mid([NumberField],3))
    </code>
    - Select Query | Run.

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Replace (Access 2000)

    Hello Hans: Thanks for the method to replace my numbers. I will try and see if I can make it work. Sid

  4. #4
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Replace (Access 2000)

    Hello Again Hans: I did use your formula to make my changes, and it did work! However, some of my numbers are a little weird, like 5675.03*123 and upon running the query the system dropped all the numbers after the first decimal point. Probably you could have added some more to the equation and prevented this. Anyway, rather than bother you any more I just made the changes one at a time. Thanks for the help though, and Happy New Year to you. Sid

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

    Re: Search and Replace (Access 2000)

    Is the field in question really a number field, or is it actually a text field?

  6. #6
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Replace (Access 2000)

    Hello Again Hans: Well, Damn, it IS a text field. I suppose this will make a lot of difference to the expression for the change. I told you I was an elementary user. Sorry. Sid

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

    Re: Search and Replace (Access 2000)

    In that case, you can omit the Val function: enter

    (113-Left([NumberField],2)) & Mid([NumberField],3)

    in the Update to line, again replacing NumberField with the actual name of the field.

  8. #8
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Replace (Access 2000)

    Hello Hans: Thanks for all of your help. The expression, leaving out the Val works fine. Now, where does one find a book that you can use to learn the logic and language of Access? For me it would have to start at a pretty elementary level and work up. I can muddle my way through most of the simple access things using Running Microsoft Access, but when it comes to writing expressions I am totally lost. The explanations of the language in the book seem to assume too much previous experience for me to understand it. Thanks again. Sid

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

    Re: Search and Replace (Access 2000)

    Access uses standard VBA functions in expressions, plus some functions that are specific to Access. So learning to write expressions means learning some VBA.

    See <post#=597,983>post 597,983</post#> and <post#=502,567>post 502,567</post#> for some suggestions about learning tools and books.

    And Books on John Viescas' website lists lots of books about Access, VBA etc.

Posting Permissions

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