Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

How do you perform a case-sensitive DISTINCT in Microsoft Access 2007?

I have figured out how to do case-sensitive joins with the StrComp() function, but I can't think of the way to filter a list for case-sensitive unique records. I assume it would require some type of self join, but then I end up with duplicates that I don't know how to get rid of. Can somebody provide me with the SQL to do this?
0
CWenger
Asked:
CWenger
1 Solution
 
pcelbaCommented:
You have to extend the column list in SELECT DISTINCT by some calculated column, e.g.

SELECT DISTINCT YourText, StrToHex(YourText) AS hextext
  FROM ...

The StrToHex() is user defined function (you may find it in Access 2007 help):
Function StrToHex(S As Variant) As Variant
   Dim Temp As String, I As Integer
   
   If VarType(S) <> 8 Then
               
     StrToHex = S
   Else
      Temp = ""
       For I = 1 To Len(S)
          Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
       Next I
       StrToHex = Temp
  End If
End Function

Open in new window

0
 
CWengerAuthor Commented:
THANK YOU!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now