Case sensitive query

[Panzerattack]

Hello all,

I've done this before but I can't remember the formula.

I have some data

Username Company
111111B Fakename
111111b Fakename
111111C Fakename
111111c Fakename

I want to count how many people belong to Fakename but Access keeps telling me it's 2, when I can clearly see it's 4. The problem is it's unable to tell the difference between the upper and lower cases.



This is what I have so far:

SELECT Contactsmultipleaccounts.[Contact ID], Count(Contactsmultipleaccounts.[Account ID]) AS [CountOfAccount ID]
FROM Contactsmultipleaccounts
GROUP BY Contactsmultipleaccounts.[Contact ID]
ORDER BY Count(Contactsmultipleaccounts.[Account ID]) DESC;



Can someone please advise?

many thanks

Anthony

[ranman256]

access queries are NOT case sensitive.
the 2 are 1111 C,
2 are are 1111 B.
your group could be wrong.

[JoeM]

I have a rather clumsy workaround that may work for you.

I created a User Defined Function in VBA that converts any string to a series of its ASCII value representations (so "A" is 65 and "a" is 97). So, if for the purposes of this query, we convert the Username field using this function and group on that, it should give you what you want.

So, the VBA code for that function looks like:

Function ASCIIVal(myString As String) As String

    Dim myASCIIStr As String
    Dim myLen As Long
    Dim i As Long
    
    myLen = Len(myString)

    If myLen > 0 Then
        For i = 1 To myLen
            myASCIIStr = myASCIIStr & Asc(Mid(myString, i, 1)) & "-"
        Next i
        ASCIIVal = Left(myASCIIStr, Len(myASCIIStr) - 1)
    End If
    
End Function

and your query would look like:

SELECT ASCIIVal(Contactsmultipleaccounts.[Contact ID]) as AscID, First(Contactsmultipleaccounts.[Contact ID]) as ContactID, Count(Contactsmultipleaccounts.[Account ID]) AS [CountOfAccount ID]
FROM Contactsmultipleaccounts
GROUP BY ASCIIVal(Contactsmultipleaccounts.[Contact ID])
ORDER BY Count(Contactsmultipleaccounts.[Account ID]) DESC;

[NTC]

If your UserName field is an accurate sample then what you can do in a query is make a calculated field that is ALLCAPS: UCase ( [UserName] )

Then create a 2nd calculated field that compares UserName to ALLCAPS called Compare: StrComp([UserName],[ALLCAPS], vbBinaryCompare)
....this field will return 0, 1, -1

Then create a 3rd calculated field called Unique: Iif([Compare] = 0, [UserName],[UserName] & "1")
...this results in a different string that can then be grouped on

just air code but that's the idea......

however if your sample is in fact: JOE vs jOe vs joE are to be found to be different then of course this approach will not work - it would only work if a single character variable is the issue.....