Grouping case insensitive

[tiredofit]

The code below is used to extract data from a csv file.

Option Explicit
Option Text Compare

Sub GetData()    

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim strcon As String
    Dim strSQL As String

​
Set cn = New ADODB.Connection
    
        strcon = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\MyData\;" & _
                 "Extended Properties=""text;" & _
                 "HDR=Yes;" & _
                 "FMT=Delimited"""
                                    
        cn.Open ConnectionString:=strcon

    Set rs = New ADODB.Recordset
        
    rs.CursorLocation = adUseServer
          
    Dim File As String
    Dim rng As Range
        
    File = "[MyData.csv]"
    
    strSQL = "SELECT [Location], SUM([Price]) AS [Sum Price]" & _
             "FROM " & File & _
             "GROUP BY [Location]"
       
    Set rng = Sheet1.Cells(2, 1)
    
        rs.Open Source:=strSQL, _
                ActiveConnection:=cn, _
                CursorType:=adOpenForwardOnly, _
                LockType:=adLockReadOnly, _
                Options:=adCmdText
    
    rng.CopyFromRecordset Data:=rs
    
End Sub

The problem is even with Option Text Compare, it STILL is case sensitive, ie if I have two locations, London and LONDON in my data, the Group By clause treats these two locations as different and thus does not group them.

What can be done?

Thanks

 

[ranman256]

my Access doesnt make the distinction of case, it treats both the same.
but
make another field in the query to Ucase the group:

GroupCity: Ucase([city])

 

[tiredofit]

Thanks