Character '-' And ADO Sorting

[SGCS]

I am trying to use SQL query to sort some names stored in my Access database. The sorting query is " SELECT [name] FROM [table1] ORDER BY [name] asc." I have six names in the CUSTOMER table. The result ADO recordset contains the records in the following sequence:

name
--------
<None>
Orange
Orange
-Orange
Tea
Tea

It is strange that "-Orange" is appearing between "Orange" and "Tea". It seems that the character '-' is ignored during the sorting.

Has anyone here encountered this before?

[gknierim]

Its the way Access handles the character because typically you wouldn't start a text field with the - character. '-' is also a special wildcard character and so Access basically ignores it when it is ordering because of this.

My advice: don't start the contents of the field with a dash or any special character for that matter.

FWIW: SQL Server doesn't have this problem.


HTH,
Greg

[fuzz_ball]

I'm having this SAME issue. However, contrary to the previous response, I'm using MS SQL Server. Essentially what I have discovered is that the hyphen is ignored in a Recordset, but NOT when executing the query via query analyzer. For instance, if I do an order by, and run a query against a particular column like so:

select name from table order by name

I would get the following:

Smith
Smithe
Smithy
Smith-Wesson

If I use a Recordset via VC++ to load this same data, and apply a sort to it

pRS->sort = "name";

and then display the Recordset in a list control, my output is:

Smith
Smithe
Smith-Wesson
Smithy


You'll notice that the sorting in the DB recognizes the hyphen and treats it as a lower precedence than the "y" (in terms of alphabetical matching). Unfortunately, the Recordset (and I verified this by changing values, removing hyphens, adding hyphens etc) acts as if there is no hyphen, and in that case, the next alpha character to compare is the "W" which has higher precedence than the "y".

I'm searching through the ADO docs and so far no luck. Has anyone dealt with this before?