Solved

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

Posted on 2009-05-05
2
894 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
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing Parameter to Stored Procedure 4 27
performance query 4 32
Select case on click 3 21
Help with Oracle IF statment 5 26
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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