SQL Query using excel to build the where clause

[hitdave85]

Hello,

I am trying to write some VBA code which builds a SQL query using cells in my excel spreadsheet to build the where clause. I currently have a string like the following: SQLStr= "SELECT * FROM TABLE". I would like to build in some where criteria, which would grab field names and field values from my excel spreadsheet.

The field names are located in cells A6:A13, and the corresponding listboxes containing those fields' values are located in cells B6:B13. When no value is specified for a particular field, the listbox simply displays the word "All". So in other words, if no where clause is needed for a particular field, then its corresponding value simply displays "All" in the excel spreadsheet.

Any thoughts would be greatly appreciated!

 

[Worf]

Hello

The elegant way would be to add parameters, but let us start with string concatenation:

Sub Test()
Dim oCn As Object, oRs As Object, i%, s$
s = "SELECT DISTINCT Pnumber FROM [tb$d6:g16] WHERE"
For i = 6 To Range("a" & Rows.Count).End(xlUp).Row
    If Cells(i, 2) <> "All" Then
        Select Case InStr(1, Cells(i, 2), ">") + InStr(1, Cells(i, 2), "<") = 0
            Case True
                s = s & " " & Cells(i, 1) & " = '" & Cells(i, 2) & "'" & " AND"
            Case False
                s = s & " " & Cells(i, 1) & " " & Cells(i, 2) & " AND"
        End Select
    End If
Next
[d18] = Left(s, Len(s) - 4)
Set oCn = CreateObject("ADODB.Connection")
oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & ThisWorkbook.FullName & _
"';" & "Extended Properties=""Excel 12.0 Macro;HDR=Yes;FMT=Delimited;IMEX=1;"";"
Set oRs = oCn.Execute([d18])
oRs.MoveFirst
s = ""
Do Until oRs.EOF
    s = s & oRs.Fields(0).Value & " ,"
    oRs.MoveNext
Loop
[d19] = Left(s, Len(s) - 2)
Set oCn = Nothing
End Sub