We help IT Professionals succeed at work.

Case sensitive Group by in Query

Alon_h
Alon_h asked
on
Medium Priority
508 Views
Last Modified: 2012-06-27
Hi

Im trying to create a query (in access 97)
whitch will be a group by on text field that is case sensitive.

for example for this table...

Filed1   Filed2
---------------
  DDD      1
  ddd      2
  lll      3

The result must by:

Filed1   Filed2
---------------
  DDD      1
  ddd      2
  lll      3

and not  

Filed1   Filed2
---------------
  ddd      2
  lll      3

thanks alon.
Comment
Watch Question

As far as Iknow, you cannot.

When faced with a similar situation, I added an additional field to determine the ASCII value of the letters of the field in question, then sorted on that field.

Here is a function that an expert here provided to me to help in a similar case.  (I don't know the author.)

This  function takes a string and returns a numerical breakdown of the ascii characters in
it.  You could use the returning value to distinguish between two strings that consist of the same letters
in different cases:

Public Function ascID(ByVal pString As String) As String
   Dim results As String
   Dim i As Integer
   results = ""
   For i = 1 To Len(pString)
       results = results & Format(Asc(Mid(pString, i, 1)), "000")
   Next
   ascID = results
 
End Function

ascid("BLACK") = 066076065067075
ascid("Black") = 066108097099107
ascid("black") = 098108097099107
ascid("bLaCk") = 098076097067107

Author

Commented:
I mean...

from this table :

Filed1   Filed2
---------------
 aaa        1
 AAA        1
 BBB        2
 BBB        2

The Rusult is need to by:

Filed1   Filed2
---------------
 aaa        1
 AAA        1
 BBB        2


Commented:
Hi Alon and volibrawl

I wrote this function few months ago, but slightly different for concatenating the result:

Function fncASCII(strInput As String) As String
Dim intI As Integer

For intI = 1 To Len(strInput)
fncASCII = fncASCII & Right("000" & Asc(Mid(strInput,intI, 1)), 3)
Next intI

End Function

Thought I saw your function afterwards, but with the present "Topic search" that's hard to say.
Could be nice to try or the Right construction is faster as the Format, as this is a rather time consuming way to sort...

Nic;o)

Commented:
for Alon_h

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question
is answered by: nico5038
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Per recommendation, force-accepted by
Netminder
CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.