Solved

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

Posted on 2009-05-05
2
923 Views
Last Modified: 2012-05-06
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
Comment
Question by:CWenger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 50 total points
ID: 24309454
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
 

Author Closing Comment

by:CWenger
ID: 31578230
THANK YOU!!!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question