Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query for timesheet application 3 24
SQL query to select row with MAX date 7 40
sql update 2 35
Read text on Table 7 25
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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