String Sort / Unicode problem

[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!

 

[John Spencer (MVP)]

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 Sir]

It's an interesting solution and I will try it. I appreciate your help
greatly!

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.

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!