Excel userform pass values to SQl query

[scalestwo]

hi all .. first time here for me..
I've done a lot of research on this site and i either cant find my issue or i cant interpret correctly.
1. i have an Excel user form, the excel userform has 3 boxes that can allow for multiple selections in each box.
2. the values are returned to Excel in columns A, B, C, D. Each column could have 1 or many returned values.
3. I'm trying to pass those values in a SQL query
I tried this code below but i dont know how to be able to read all the values in a column and pass to the query..
Basically i dont want all these 'And' statements and 'OR' statements in my 'where' clause.. how can shrink this code up?

[vba]
DataWrkSht = Sheets("SelectionCriteriaResult")

Div1 = DataWrkSht.Cells(5, 1).Value
Div2 = DataWrkSht.Cells(6, 1).Value
Div3 = DataWrkSht.Cells(7, 1).Value
Div4 = DataWrkSht.Cells(8, 1).Value
Div5 = DataWrkSht.Cells(9, 1).Value
Div6 = DataWrkSht.Cells(10, 1).Value
Div7 = DataWrkSht.Cells(11, 1).Value
Div8 = DataWrkSht.Cells(12, 1).Value
Div9 = DataWrkSht.Cells(13, 1).Value
Div10 = DataWrkSht.Cells(14, 1).Value
ExpGrp1 = DataWrkSht.Cells(5, 9).Value
ExpGrp2 = DataWrkSht.Cells(6, 9).Value
ExpGrp3 = DataWrkSht.Cells(7, 9).Value
Year1 = DataWrkSht.Cells(5, 17).Value
Year2 = DataWrkSht.Cells(6, 17).Value
Year3 = DataWrkSht.Cells(7, 17).Value
Table = "EXPENSE_X_CBACK"
Field1 = "DIV"
Field2 = "EXPENSE_GROUP"
Field3 = "YEAR"
TableField1 = Table & "." & Field1
TableField2 = Table & "." & Field2
TableField3 = Table & "." & Field3
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=xxxxx;Description=xxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xx_xxxxx;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = "Select * " _
& "FROM pt_aawokmgr.dbo." _
& Table _
& " WHERE ((" & TableField & "='" & Div1 & "') " _
& "Or (" & TableField1 & "='" & Div2 & "')" _
& "Or (" & TableField1 & "='" & Div3 & "')" _
& "Or (" & TableField1 & "='" & Div4 & "')" _
& "Or (" & TableField1 & "='" & Div5 & "')" _
& "Or (" & TableField1 & "='" & Div6 & "')" _
& "Or (" & TableField1 & "='" & Div7 & "')" _
& "Or (" & TableField1 & "='" & Div8 & "')" _
& "Or (" & TableField1 & "='" & Div9 & "')" _
& "Or (" & TableField1 & "='" & Div10 & "'))" _
& "And ((" & TableField2 & "='" & ExpGrp1 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp2 & "')" _
& "Or (" & TableField2 & "='" & ExpGrp3 & "'))" _
& "And ((" & TableField3 & "='" & Year1 & "')" _
& "Or (" & TableField3 & "='" & Year2 & "')" _
& "Or (" & TableField3 & "='" & Year3 & "'))" _



.CreatePivotTable TableDestination:="'[MI Tool.xls]ListBox List'!R30C1", _
TableName:="PivotTale2", DefaultVersion:=xlPivotTableVersion10
End With
[/vba]

Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags

[Bob Phillips]

I don't understand. You want to pass many values to your SQL WHERE clause, but you don't want to use AND or OR? THe only other way I can think of is to load into a temporary table and interrogate that table in the query, but why bother?

[scalestwo]

sorry on the question...
in this 'where' clause above, what is happening is that some of the values in the query are "" and then when it is joined with the AND statement it returns empty dataset.
somehow i need to loop through my OR statement until empty cell value..
...So what is happening is the query is passing "" with say year 2007 and because it is 'AND' it returns empty data.
I dont know how to construct my WHERE clause so that it loops through all values in a column until cell is empty..

[Jan Karel Pieterse]

Create your SQl in a separate string variable and omit the parts that are "" by using if-then constructs.

[scalestwo]

I'm not sure how to create SQl in a separte string variable and omit the parts that are "" by using if -then constructs... could you give me a sample code example.. i'm sorta a newbie to vba/sql.. i struggle with the syntax..thanks.!

[geekgirlau]

[vba]
Dim DataWrkSht As Sheet
Dim strDiv As String
Dim strExp As String
Dim strYear As String
Dim strWhere As String
Dim i As Integer


DataWrkSht = Sheets("SelectionCriteriaResult")

' capture values for DIV
For i = 5 To 14
If DataWrkSht.Cells(i, 1).Value <> "" Then
strDiv = strDiv & "'" & DataWrkSht.Cells(i, 1).Value & "',"
End If
Next i

If strDiv <> "" Then
strWhere = "(DIV In (" & Left(strDiv, Len(strDiv) - 1) & ")"
End If

' capture values for EXPENSE_GROUP
For i = 5 To 7
If DataWrkSht.Cells(i, 9).Value <> "" Then
strExp = strExp & "'" & DataWrkSht.Cells(i, 9).Value & "',"
End If
Next i

If strExp <> "" Then
strWhere = strWhere & " AND (EXPENSE_GROUP In (" & Left(strExp, Len(strExp) - 1) & ")"
End If

' capture values for YEAR
For i = 5 To 7
If DataWrkSht.Cells(i, 17).Value <> "" Then
strYear = strYear & "'" & DataWrkSht.Cells(i, 17).Value & "',"
End If
Next i

If strYear <> "" Then
strWhere = strWhere & " AND (YEAR In (" & Left(strYear, Len(strYear) - 1) & ")"
End If

If Left(strWhere, 5) = " AND " Then
strWhere = Mid(strWhere, 6)
End If

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=xxxxx;Description=xxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;" & _
"DATABASE=xx_xxxxx;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = "SELECT * " _
& "FROM pt_aawokmgr.dbo.EXPENSE_X_CBACK " _
& "WHERE " & strWhere

.CreatePivotTable TableDestination:="'[MI Tool.xls]ListBox List'!R30C1", _
TableName:="PivotTale2", DefaultVersion:=xlPivotTableVersion10
End With
[/vba]