macro/query to update records?

  • Thread starter Thread starter msnews.microsoft.com
  • Start date Start date
M

msnews.microsoft.com

I have a database that I am importing periodically from DOS Foxbase. I need
to make changes in Access.

Several fields are using codes for accented characters such as o' for ó, o;
for ö, u" for u

Replace works fine, but I would like to automate the process. I know it is a
one line code, but I don't have the time to figure it out (to learn access
programming for that one task)



I also have a field with a code, 26 to be exact. I want them in a non
alphabetical order, so I would like to replace for example 'A' with 06, 'B'
with 12 so that I can determine the order for reporting. (The database is a
book catalogue, the codes are subject headings with one letter mnemonic
codes such as 'L' for literature and the printed catalogue needs to be in a
logical, not alphabetical order)

There should be a better way than to write 26 replace queries. I could not
figure out how to do it with one. Can i strind queries together in a macro?

Where can I find some sample that can show me how to do these simple tasks?



Any help would be greatly appreciated



Zork Hun

www.trixnet.com
 
Zork,

Make a simple table with 2 fields, 26 records, each record containing
your Letter code in one field, and the numerical order for logical
sorting in the other. Make a query with your existing table and this
code table, joined on the letter code field from both tables, and then
use the numerical code field for sorting.

- Steve Schapel, Microsoft Access MVP
 
Wow!
I love the beauty of simplicity!
Thanks.
Any suggestion about the replacements?
 
Zork,

Using a macro (this is a macros newsgroup, after all!), the only thing
I can think of is to make an Update Query for each of the coded
characters, for example update YourField to...
Replace([YourField],"o'","ó")
and then make a macro using an OpenQuery action to run all of the
updates sequentially. Shouldn't be a problem.

Other than that, you could write a User-Defined function in a VBA
module to put all the conversions into one package.

- Steve Schapel, Microsoft Access MVP
 
Thank you very much, I have the whole thing worked out.
I decided not to bother with the character replacements in Access, the whole
thing will go to a merged Word document anyway, so I just wrote a macro in
word and do the character replacements there. Access had problems with the
two unicode characters.
Another problem you should know about:
I discovered that replacing a substring in a field will only replace the
first occurance in each field then moves onto the next.
If the string in the field is "a'ta'lva'n", I had to run replace three times
to get "átálván"
I had to keep running replace until I got a no replacement result.
In Word it is easier.
I will use however your suggestion with a series of more straightforward
replacements.
Thanks again
 
Zork,

I tested this and did not see the same behaviour as you. In other
words, one run of an update query to change a'ta'lva'n to átálván.
Can you please tell me what version of Access you are using, including
Service Release if you can.

- Steve Schapel, Microsoft Access MVP
 
Steve,

I double checked, that is how it works, it only replaces the first
occurance.
I am running Access 2003 Beta built 11.4920.4920.
But then I did not run a query, I ran replace from the menu because I do not
know how to specify substring replacement in a query

Zork
 
Steve,
I am sorry, I am just slow and impatient.
I figured out the update query, and I am in awe looking at the power.
Doing a query works fine, replaces every instance of the character just as
it should.
........which however does not make the problem with the replace command any
less of a bug.
I already did the macro in Word but doing the queries here would work just
as well.

Thanks again for the help (and patience)

Zork
 
Zork,

I must admit I made the assumption that when you mentioned Replace,
you were talking about the Replace() function rather than the Replace
menu item. The Replace menu item is something I have never had cause
to use, so I am not really familiar with it. However, I gave it a
whirl, and I see that the behaviour you mention is consistent through
Access versions 97, 2000, and 2002. If you click Find Next and the
Replace it takes you from one instance to the next. However, if you
click Replace All it ... well, it replaces all :-) Anyway, I am
pleased to hear that you have discovered the Update Query.

- Steve Schapel, Microsoft Access MVP
 
Back
Top