Group by query - case sensitive 1

[rjoubert]

I have a Group By SQL statement I am trying to run against an Excel spreadsheet (via a VB.NET app). I want the query to return two records if it finds data like "Test" and "test". Instead, it's only returning one record. Suggestions?

SELECT [FieldName], Count([FieldName]) as DataCount 
FROM [TheData]
GROUP BY [FieldName]
ORDER BY Count([FieldName])

 

[dhookom]

If this was within the Access environment, I would create a function that converts the characters in FieldName to their ASC equivalent:

Function ConvertASC(pstrText As String) As String
    Dim strOut As String
    Dim intI As Integer
    For intI = 1 To Len(pstrText)
        strOut = strOut & Format(Asc(Mid(pstrText, intI, 1)), "000")
    Next
    ConvertASC = strOut
End Function

This will differentiate between upper and lower case. However since you are using a .net environment, I'm not sure how you would implement this.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rjoubert]

Is that what Access' Asc function does? If so, I tried that per PHV's suggestion.

 

[lespaul]

the ASC function converts the field to it's ASCII number. The string 'text' is ASCII is:
text

The string 'TEXT' in ASCII is:
TEXT

So by converting your field information to ASCII, you should be able to do your case sensitive grouping.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dhookom]

The ASC() function will return the ASCII value of the first character of a string. For instance
ASC("This") = 84
That is why I wrote the function to convert each character in the string.

The issue is that my function will work in Access but not from another environment such a .net.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

And yes, I tried your suggestion, and it did not work
You still haven't answered my question:
Exactly the same result when Asc([FieldName]) added to the Group By clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rjoubert]

In my post from 11 Jul 07 15:50, I stated that there was a syntax error when I added the Asc([FieldName]) to the Group By clause. And in my post from 12 Jul 07 6:26, I stated that there was no error when the Asc([FieldName]) is not in the Group By clause. So I believe that answers your question...if it doesn't, please restate your question. Thanks.

 

[PHV]

OK, sorry, for some reason I didn't see you stated that there was a syntax error.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rjoubert]

Well, I can see that I won't be able to solve this with my VB.NET application, so I have gone down the path of linking to the spreadsheets in an Access DB, and using the function that Duane provided. Thanks Duane.

Sorry to all if I've caused you any confusion...thanks for your efforts.