query to copy rows based on criteria

[arnab0711]

Hi,
I have this excel sheet which has a SQL query attached in the Show Data button of Input sheet.Now Query works on the inputs of cells E3 and F3 under Project Desc and Item description.Now I want to add another parameter on cell G3.pl advice the query for it.

[Bob Phillips]

Your code is unreadable.

[arnab0711]

here you go
[VBA]Sub test()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sqlstr As String
With ActiveWorkbook.Sheets(3): If .[a2] <> "" Then .Range(.[a2], .[a2].End(xlDown)).Resize(, 56).Clear
End With: With ActiveWorkbook: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .Path & "\" & .Name & ";Extended Properties=""Excel 8.0;HDR=No;"";"
sqlstr = "SELECT T1.F1,T1.F2,T1.F3,T1.F4,T1.F5,T1.F6,T1.F7,T1.F8,T1.F9,T1.F10,T1.F11,T1.F12, T1.F13,T1.F14,T1.F15,T1.F16,T1.F17," & _
"T1.F18,T1.F19,T1.F20,T1.F21,T1.F22,T1.F23,T1.F24,T1.F25,T1.F26,T1.F27,T1.F 28,T1.F29,T1.F30,T1.F31,T1.F32,T1.F33,T1.F34,T1.F35," & _
"T1.F36,T1.F37,T1.F38,T1.F39,T1.F40,T1.F41,T1.F42,T1.F43,T1.F44,T1.F45,T1.F 46,T1.F47,T1.F48,T1.F49,T1.F50,T1.F51,T1.F52,T1.F53," & _
"T1.F54,T1.F55,T1.F56 FROM `raw data$A2:BD65536` T1 WHERE"
With Sheets("Input")
Select Case .[e3]
Case Is <> ""
sqlstr = sqlstr & " UCASE(T1.F4) LIKE '%" & UCase(.[e3]) & "%'"
If .[f3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
End Select
rs.Open sqlstr, cn
With Sheets(3): .[a2].CopyFromRecordset rs: [a:bd].EntireColumn.AutoFit: End With
End With: Set rs = Nothing: Set cn = Nothing: End With: End Sub
[/VBA]