String Sort / Unicode problem

  • Thread starter Thread starter Mr Sir
  • Start date Start date
M

Mr Sir

Hi Folks,
I'm sorting a bunch of codes in Access2000 that are stored in a text
field and contain a good number of dashes (-). As I understand it Unicode
data types do not recognize dashes. Therefore, i am getting results like:
A-BC
AB-D
A-BE
ABF
AB-G

When what I want is:
A-BC
A-BE
AB-D
AB-G
ABF

The same problem in SQL Server was solved by changing the datatype from
nvarchar to varchar. There is no such option in Access. Does anyone know
of a way to force the sort to produce results that a Non-Unicode field would
produce?

Thanks!
 
One way I can see would be to use the replace function to build a calculated
column where you replace the - with an underscore. The problem is that Replace
doesn't work in Access 2000 SQL statements. You need to create a user function
to call it.

UNTESTED AIRCODE

Function fReplace(strIn, strFind, strreplace, Optional lngstart = 1, Optional
lngCount = 1)

fReplace = Replace(strIn, strFind, strreplace, lngstart, lngCount, vbTextCompare)
End Function

Sample SQL Statement

SELECT Table1.String1
FROM Table1
ORDER BY fReplace([string1],"-","_");

I hesitated to answer this, because I really didn't have a good answer. I only
attempted an answer because no one else was tackling it. So I hope you can use
this solution.
 
It's an interesting solution and I will try it. I appreciate your help
greatly!


John Spencer (MVP) said:
One way I can see would be to use the replace function to build a calculated
column where you replace the - with an underscore. The problem is that Replace
doesn't work in Access 2000 SQL statements. You need to create a user function
to call it.

UNTESTED AIRCODE

Function fReplace(strIn, strFind, strreplace, Optional lngstart = 1, Optional
lngCount = 1)

fReplace = Replace(strIn, strFind, strreplace, lngstart, lngCount, vbTextCompare)
End Function

Sample SQL Statement

SELECT Table1.String1
FROM Table1
ORDER BY fReplace([string1],"-","_");

I hesitated to answer this, because I really didn't have a good answer. I only
attempted an answer because no one else was tackling it. So I hope you can use
this solution.


Mr said:
Hi Folks,
I'm sorting a bunch of codes in Access2000 that are stored in a text
field and contain a good number of dashes (-). As I understand it Unicode
data types do not recognize dashes. Therefore, i am getting results like:
A-BC
AB-D
A-BE
ABF
AB-G

When what I want is:
A-BC
A-BE
AB-D
AB-G
ABF

The same problem in SQL Server was solved by changing the datatype from
nvarchar to varchar. There is no such option in Access. Does anyone know
of a way to force the sort to produce results that a Non-Unicode field would
produce?

Thanks!
Click to expand...
Click to expand...
 
Back
Top