VBA sql query filter problem.

[kashif.special2005]

Hi,

I am using below code to filter the result as per selection from the user form, but it is giving an invalid sql, please help me to achieve this task.

Sub EmpFilter()




Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recordset
Dim VarMyData() As Variant
Dim Inti As Integer
Dim StrSqlWhere As String




Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset


objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\Kashif\Desktop\MyData.xlsm;" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;"";"


If UserForm1.lstProject.Value <> "All" Then
    StrSqlWhere = "Where [Projects]='" & UserForm1.lstProject.Value & "'"
Else
    StrSqlWhere = "Where [Projects]='*'"
End If


If UserForm1.cmbName.Value <> vbNullString Then
    StrSqlWhere = StrSqlWhere & " And [Name]='" & UserForm1.cmbName.Value & "'"
Else
    StrSqlWhere = StrSqlWhere & " And [Name]='*"
End If




objRecordset.Open "Select [Employee] FROM [Sheet1$A1:BW4609] " & StrSqlWhere & " group by [Employee] order by [Employee]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText


    ReDim VarMyData(0 To objRecordset.RecordCount - 1, 1 To 1)  [I][B]'Error Code Here "Subscript Out of Range"[/B][/I]


    For Inti = 0 To objRecordset.RecordCount - 1
        VarMyData(Inti, 1) = objRecordset.Fields(0)
        objRecordset.MoveNext
    Next Inti
    
    UserForm1.lstEmployee.List = VarMyData


End Sub

Thanks
Kashif

 

[anvg]

Hi

but it is giving an invalid sql

It is not true. Simple add before objRecordset.Open

objRecordset.CursorLocation = adUseClient

Without it recordset cannot return record count.
Regards,

 

[kashif.special2005]

Hi anvg,

Thanks for reply, however it is giving the error.

Is this query is correct.

Select [Employee] FROM [Sheet1$A1:BW4609] Where [Projects]='*' And [Name]='*' group by [Employee] order by [Employee]

If not how can I write query for the condition, for the below condition.

If UserForm1.lstProject.Value <> "All" Then
StrSqlWhere = "Where [Projects]='" & UserForm1.lstProject.Value & "'"
Else
StrSqlWhere = "Where [Projects]='*'"
End If



If UserForm1.cmbName.Value <> vbNullString Then
StrSqlWhere = StrSqlWhere & " And [Name]='" & UserForm1.cmbName.Value & "'"
Else
StrSqlWhere = StrSqlWhere & " And [Name]='*"
End If

Thanks
Kashif