Search and Replace (Access 2000)

[G741]

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

[HansV]

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.

[G741]

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

[G741]

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

[HansV]

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

[G741]

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

[HansV]

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.

[G741]

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

[HansV]

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.