Case sensitive replace query and international sorting

  • Thread starter Thread starter Zork \(the\) Hun
  • Start date Start date
Z

Zork \(the\) Hun

My task is to create a catalogue from a DOS FoxPro database. I am using
Access 2003
Hungarian characters are coded in the database as in a' for á; e' for é etc.
I use Access to make it ready for a Word merge.
I have the following replace query:
Replace([catalogue]![invt_auth],"a'","á")



Problem #1:



Although the query works, for every replacement i get an error saying:

"Microsoft Access didn't update x field(s) due to type conversion failure,
....." offers to ignore the error(s) then makes the changes. If I click on
help, I get a blank window.



Problem #2:

The query is not case sensitive, it will replace both a' and A' with
lowercase á
How can I make the query case sensitive? Talking about names and titles, the
capital letter can be anywhere, not just at the beggining of the string. I
have no problems with creating two sets of queries for lower and upper case
replacements.



Problem #3:
(and I know that this may belong to another group, but since I'm at it...)

I need Hungarian sort order. a and á are different letters, the proper sort
order is not:

ab - áb - ac - ác - az - áz, but:

ab - ac - az - áb - ác - áz

There is a setting in options/general tab to set new database sort order,
but I cannot see it doing anything and I still need to know how to change
the sort order of existing tables.



I can correct some of these problems manually in Word, but it is a real drag
to do that.



Your suggestions will be greatly appreciated





Zork (the) Hun(garian)
 
I don't have an answer for problem #1.

For problem #2, try:

Replace([catalogue]![invt_auth],"a'","á", -1, 0)

... that last option asks it to do a Binary compare, which should be
case-sensitive.

For problem #3, the option is NEW database sort order. Any database you
create after you set this option will have the sort order specified. It
does not affect the current database. So, set the NEW sort order, create a
new database, and import the table(s) from the old one.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks, I did try, it effectively emptied the fields I ran it on for every
single record, even those that did not contain any of the coded characters.
The reason is probably problem #1 - the type conversion failure.
When I was importing with the wizard, I tried different settings for code
pages, assuming that the wizard will treat the file to be imported as having
the code-page specified there (as opposed to something it converts it in the
process of importing. Either way, I could not produce a noticeable
difference.

As for problem #3, naturally, I did try that before I asked the question. I
created a new database, created a test table with test records, and as I
said, it did not seem to make a difference. Sorting is still wrong. Feel
free to try, enter the following records:
ab, ac, ad, az, Ad, Ac, áb, ác, Áz, áz

....and let me know if it is something I did wrong.

I am afraid Access is not powerful enough for my simple task, but I am
grateful for the help [even when it doesn't :-) ]

Zork

John Viescas said:
I don't have an answer for problem #1.

For problem #2, try:

Replace([catalogue]![invt_auth],"a'","á", -1, 0)

.. that last option asks it to do a Binary compare, which should be
case-sensitive.

For problem #3, the option is NEW database sort order. Any database you
create after you set this option will have the sort order specified. It
does not affect the current database. So, set the NEW sort order, create a
new database, and import the table(s) from the old one.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Zork (the) Hun said:
My task is to create a catalogue from a DOS FoxPro database. I am using
Access 2003
Hungarian characters are coded in the database as in a' for á; e' for é etc.
I use Access to make it ready for a Word merge.
I have the following replace query:
Replace([catalogue]![invt_auth],"a'","á")



Problem #1:



Although the query works, for every replacement i get an error saying:

"Microsoft Access didn't update x field(s) due to type conversion failure,
...." offers to ignore the error(s) then makes the changes. If I click on
help, I get a blank window.



Problem #2:

The query is not case sensitive, it will replace both a' and A' with
lowercase á
How can I make the query case sensitive? Talking about names and titles, the
capital letter can be anywhere, not just at the beggining of the string. I
have no problems with creating two sets of queries for lower and upper case
replacements.



Problem #3:
(and I know that this may belong to another group, but since I'm at it...)

I need Hungarian sort order. a and á are different letters, the proper sort
order is not:

ab - áb - ac - ác - az - áz, but:

ab - ac - az - áb - ác - áz

There is a setting in options/general tab to set new database sort order,
but I cannot see it doing anything and I still need to know how to change
the sort order of existing tables.



I can correct some of these problems manually in Word, but it is a real drag
to do that.



Your suggestions will be greatly appreciated





Zork (the) Hun(garian)
Click to expand...
Click to expand...
 
Try checking out this website: http://www.trigeminal.com/

Michael Kaplan is a world expert on languages and code pages.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Zork (the) Hun said:
Thanks, I did try, it effectively emptied the fields I ran it on for every
single record, even those that did not contain any of the coded characters.
The reason is probably problem #1 - the type conversion failure.
When I was importing with the wizard, I tried different settings for code
pages, assuming that the wizard will treat the file to be imported as having
the code-page specified there (as opposed to something it converts it in the
process of importing. Either way, I could not produce a noticeable
difference.

As for problem #3, naturally, I did try that before I asked the question. I
created a new database, created a test table with test records, and as I
said, it did not seem to make a difference. Sorting is still wrong. Feel
free to try, enter the following records:
ab, ac, ad, az, Ad, Ac, áb, ác, Áz, áz

...and let me know if it is something I did wrong.

I am afraid Access is not powerful enough for my simple task, but I am
grateful for the help [even when it doesn't :-) ]

Zork

John Viescas said:
I don't have an answer for problem #1.

For problem #2, try:

Replace([catalogue]![invt_auth],"a'","á", -1, 0)

.. that last option asks it to do a Binary compare, which should be
case-sensitive.

For problem #3, the option is NEW database sort order. Any database you
create after you set this option will have the sort order specified. It
does not affect the current database. So, set the NEW sort order,
Click to expand...
create
a
new database, and import the table(s) from the old one.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Zork (the) Hun said:
My task is to create a catalogue from a DOS FoxPro database. I am using
Access 2003
Hungarian characters are coded in the database as in a' for á; e' for
Click to expand...
é
etc.
I use Access to make it ready for a Word merge.
I have the following replace query:
Replace([catalogue]![invt_auth],"a'","á")



Problem #1:



Although the query works, for every replacement i get an error saying:

"Microsoft Access didn't update x field(s) due to type conversion failure,
...." offers to ignore the error(s) then makes the changes. If I click on
help, I get a blank window.



Problem #2:

The query is not case sensitive, it will replace both a' and A' with
lowercase á
How can I make the query case sensitive? Talking about names and
Click to expand...
titles,
the
capital letter can be anywhere, not just at the beggining of the
Click to expand...
Click to expand...
string.
I proper
sort real
drag
Click to expand...
Click to expand...
 
Back
Top