[AccessD] Tilde Sort at the top

Heenan, Lambert Lambert.Heenan at AIG.com
Wed Jul 30 13:33:47 CDT 2003


The reason that it's happening is that Access sorts based on a collating
order, not on the ASCII codes. The collating order takes account of things
like characters with accents over them etc so that strings like "Help",
"Hèlp" and "Hélp" will all appear in the 'correct' alphabetical order,
regardless of their ASCII codes.

To get things sorted the way you want it just add an extra sort criteria to
your query like this...

SELECT  tblEmployees.TrainerName
FROM tblEmployees
ORDER BY StrComp([TrainerName],"Z",0), tblEmployees.TrainerName;


The StrComp([TrainerName],"Z",0) in the Order By clause does the trick. It
tells access to compare the strings using a binary comparison, which is
ASCII based.

Lambert

> -----Original Message-----
> From:	Rocky Smolin - Beach Access Software [SMTP:bchacc at san.rr.com]
> Sent:	Wednesday, July 30, 2003 1:57 PM
> To:	AccessD at databaseadvisors.com
> Subject:	[AccessD] Tilde Sort at the top
> 
> Dear List:
>  
> I need a special character to sort the contents of a combo box so that
> items with the special character sort at the bottom of the list.  (The
> items come from a table).  So I tried using a tilde ~ because its ASCII
> value is 126, greater than any number or upper or lower case letter.  But
> it sorts to the top of the list.
>  
> Is there a simple explanation and/or workaround?
>  
> Much thanks in advance and regards,
>  
> is
>   << File: ATT1222452.txt >> 


More information about the AccessD mailing list