query to replace convert foreign language characters to correct case

[BennyLinton]

I have a query (that I'm going to use as a base for an update query when I get it working) to convert certain Romanian characters to English alphabet. Below is what I have so far but my lower case letters are not working e.g. 'ș' is rendering as 'S':

SELECT ApplicantMaster.LastName, Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
[LastName],
'Ş',UCase('S')),
'Ș',UCase('S')),
'ș',LCase('s')),
'Ț',UCase('T')),
'ț',LCase('t')),
'ţ',LCase('t')) AS Correctname,

ApplicantMaster.FirstName, ApplicantMaster.StateProvince, ApplicantMaster.City, ApplicantMaster.Country, ApplicantMaster.GCDFNo
FROM ApplicantMaster
WHERE (((ApplicantMaster.Country)="Romania")

 

[Minty]

The Replace function isn't case sensitive in it's searching. So both your upper and lower case characters are being replaced with the first pass.

You might have to use a chr() value instead to find the exact match.

 

[BennyLinton]

I've looked for sources of codes for these characters and have so far been unable to find those.

 

[Minty]

Does this help http://www.addressmunger.com/hex_ascii_tables/

edit: you may need to use Chrw() which allows unicode characters.

 

[BennyLinton]

How do I use the charts at http://www.addressmunger.com/hex_ascii_tables/ in an Acccess query?

 

[Minty]

If you want to do this on a regular basis I would create a table with two fields the ASCII code you want on one side and the replacement code on the other. Then it's update query, or a more likely create a function and call it in the query.

 

[sneuberg]

This is kind of weird. This simplified query:

SELECT ApplicantMaster.LastName, Replace(Replace(Replace(Replace([LastName],'Ş','S'),'ș','s'),'Ț','T'),'ț','t') AS Correctname, ApplicantMaster.FirstName, ApplicantMaster.StateProvince, ApplicantMaster.City, ApplicantMaster.Country, ApplicantMaster.GCDFNo
FROM ApplicantMaster
WHERE (((ApplicantMaster.Country)="Romania"));

Works for ș but not for 'ț' producing a Correctname of SsTT for a LastName of ŞșȚț

If it helps you can determine the character code of this characters with the AscW function. I set up a form with the following button code:

Private Sub GetCharCode_Click()

MsgBox AscW(Me.CharInput)

End Sub

It outputs:

Ş - 350
ș - 537
Ț - 538
ț - 539

 

[sneuberg]

As Minty said the Replace function is not case sensitive but it appears to become case sensitive if you specify the vbBinaryCompare option which has a value of 0. It seems that you can't use constant names in functions used in queries. Also the optional parameters of start and count don't seem to be optional. So with all of that, this query

SELECT ApplicantMaster.LastName, Replace(Replace(Replace(Replace([LastName],'Ş','S',1,100,0),'ș','s',1,100,0),'Ț','T',1,100,0),'ț','t',1,100,0) AS Correctname, ApplicantMaster.FirstName, ApplicantMaster.StateProvince, ApplicantMaster.City, ApplicantMaster.Country, ApplicantMaster.GCDFNo
FROM ApplicantMaster
WHERE (((ApplicantMaster.Country)="Romania"));

produces SsTt for a LastName ŞșȚț

 

[BennyLinton]

Works perfectly now! thanks!

 

[sneuberg]

Are you going to being do this for a lot of characters? If so then Minty's idea of having a character map in a table would probably be better than using the replace function like this. I've made some progress working on that idea. Should I keeping on working on it or is this replace method going to be good enough for you?

 

[BennyLinton]

i hit a snag... The query below (my final goal) does not work on the special characters found in the field: LastNameCorrected: INSERT INTO dbo_ApplicantMaster ( GCDFNo, CertificationType, FirstName, LastName, Address1, StateProvince, City, Country, PostalCode, HomePhone, BusinessPhone, Cert, Region, ExpirationDate, GCDF_US )
SELECT ApplicantMaster.GCDFNo, ApplicantMaster.CertificationType, ApplicantMaster.FirstName, Replace(Replace(Replace(Replace([LastName],'Ş','S',1,100,0),'ș','s',1,100,0),'Ț','T',1,100,0),'ț','t',1,100,0) AS LastNameCorrected, ApplicantMaster.Address1, ApplicantMaster.StateProvince, ApplicantMaster.City, 'Romania' AS Country, ApplicantMaster.PostalCode, ApplicantMaster.HomePhone, ApplicantMaster.BusinessPhone, 'GCDF' AS Cert, 5 AS Region, ApplicantMaster.ExpirationDate, 0 AS GCDF_US
FROM ApplicantMaster
WHERE (((ApplicantMaster.CertificationType)="Full Certification") AND ((ApplicantMaster.Country)="Romania") AND ((ApplicantMaster.ExpirationDate)>Now()));

 

[sneuberg]

How is the query failing? Are you getting errors or is the result not what you expected?

 

[BennyLinton]

The field "LastNameCorrected" has not replaced the problem characters:

Bălţoi
Ghiţă
MECLEȘ
GAȘPAR
ȘTEFĂNESCU
Șerban
Puşchilă

 

[sneuberg]

These are not the character that are in the replace expression for example

Ș in GAȘPAR has character code 536. Ş is 350 in the expression. These are difference characters. I suspect if you copy and paste these missing characters into the replace expression they will be replaced.

 

[sneuberg]

Another example:
You can see ş is not ș
when you increase the font size

 

[BennyLinton]

OK I got it now! thanks: SELECT Replace(Replace(Replace(Replace([LastName],'Ş','S',1,536,0),'ș','s',1,537,0),'Ț','T',1,538,0),'ț','t',1,539,0) AS Correctname
FROM ApplicantMaster
WHERE (((ApplicantMaster.CertificationType)="Full Certification") AND ((ApplicantMaster.Country)="Romania") AND ((ApplicantMaster.ExpirationDate)>Now()));

 

[Galaxiom]

The Replace function isn't case sensitive in it's searching. So both your upper and lower case characters are being replaced with the first pass.

Replace() has three optional arguments. The last argument (compare), by default uses the module Option Compare setting. This is usually, by default, a case insensitive setting, Compare Database.

However when the compare argument receives 0, Replace() performs a binary comparison which is case sensitive.

Alternatively, if case sensitive comparisons are the norm in the module, Option Compare Binary can be used to cause case sensitive comparisons. This will also affect any other functions using text comparisons, InStr(), InStrRev(), Split() and StrComp(). These function also include an optional compare argument.