Case sensitive sorting

  • Thread starter Thread starter Norman Belanger
  • Start date Start date
N

Norman Belanger

Hi,

I have a table holding character codes from A to Z and a to z where lower
and upper case have different meaning. How do I filter lower case and upper
case in a select query.

Thanking you in advance,

Norm
 
Do you want to filter based on letter case or sort based on letter case? In
Access, neither is simple.

To filter in a where clause you would need to use the vba function InStr.

SELECT YourField
FROM YourTable
WHERE Instr(YourField,"SearchString",0)>0

Another option is to use the strComp function

SELECT YourField
FROM YourTable
WHERE StrComp(YourField,"SearchString",0)=0

Sorting is more complex, especially if you want to do it for numerous
characters. I've done it by using a function to step through the characters in
the string and return a string of the ascii values (formatted to three
characters) and then using that as my sort value. It gets slow with large selections.
 
I have a table holding character codes from A to Z and a to z where lower
and upper case have different meaning. How do I filter lower case and upper
case in a select query.
Click to expand...

As John says... isn't easy. One suggestion (still inefficient) for the
sorting is to sort on the StrComp return value as well as the code:

ORDER BY StrComp(
Code: 
, LCase([Code]), 0), [Code]

This will put all the lower case values first a to z, then all the
upper case A to Z.
 
Back
Top