Character Collation/Sorting Problem

  • Thread starter Thread starter Lou da Silva
  • Start date Start date
L

Lou da Silva

I'm not quite sure how to categorize this problem, but I've run into a
peculiar problem retrieving data from an access table [Using Access 2003,
Windows XP Professional, Jet 4.0.8618.0].

I have a table with a "key" column. Each record's "key" field contains 8
characters. As an example some of the keys look like this:

KQRSLN/?
KQWRMNNA
K-QWSLNW
KR/N/WXA
KQWWWWAC
MNWZSDRF
K-QSMTHN
KQRSLTNE

When I run the following select query:

"select key from mytable where key >= 'KQ??????' and key <= 'KQZZZZZZ';"

The results I get are

KQRSLTNE
K-QSMTHN
KQWRMNNA
K-QSMTHN
KQWWWWAC

What I don't understand is why the keys with the dashes [-] in the second
position are being returned by this query. I don't get this behaviour in
a SQL server database, but unfortunately I'm stuck using Access for this
project.

Has anyone encountered this before - and solved it?
 
Solved my own problem. Posting it here in case it comes in handy to
someone else:

I dug around some of the built in functions in Access and discovered that
if I use the StrConv() function to convert the key values and the strings
I'm comparing them to using the following I get just the records I expect
to get:

SELECT KEY FROM MYTABLE WHERE STRCONV(KEY, 128) >= STRCONV('KQ???????',
128)
AND STRCONV(KEY, 128) <= STRCONV( 'KQZZZZZZZ', 128);

The "128" in the function specifies that the strings be converted from
Unicode the the default code page of the system [in VBA use the constant
vbFromUnicode].
 
Back
Top