I need to query several records (averaging 2,000) that change every week.
First of all Microsoft query tells me that the SQL statement is too long.
Let's say that I reduce the size of the statement to a workable size if not
other options exist,

What statement can I use to refer to every single element of a dynamic range
in the WHERE part of the SQL statement instead of typing every single one
AND, AND ?

I tried the following but obviously didnt work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

You can set a range variable
with sheets("sheet2")
LastColumn = .cells(1,Columns.Count).end(xltoleft).column
LastRow = .cells(Rows.count,"A").end(xlup).row
set QRange = .range(.cells(1,"A"),.cells(LastRow,LastColumn))
MyTable.MyField=QRange
end with

"LuisE" wrote:


I need to query several records (averaging 2,000) that change every week.
First of all Microsoft query tells me that the SQL statement is too long.
Let's say that I reduce the size of the statement to a workable size if not
other options exist,

What statement can I use to refer to every single element of a dynamic range
in the WHERE part of the SQL statement instead of typing every single one
AND, AND ?

I tried the following but obviously didnt work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Thanks, Joel
My problem is not defining the range as a variable or making it to auto
expand;
it is querying the content of every single cell in the range.



"Joel" wrote:

You can set a range variable
with sheets("sheet2")
LastColumn = .cells(1,Columns.Count).end(xltoleft).column
LastRow = .cells(Rows.count,"A").end(xlup).row
set QRange = .range(.cells(1,"A"),.cells(LastRow,LastColumn))
MyTable.MyField=QRange
end with

"LuisE" wrote:


I need to query several records (averaging 2,000) that change every week.
First of all Microsoft query tells me that the SQL statement is too long.
Let's say that I reduce the size of the statement to a workable size if not
other options exist,

What statement can I use to refer to every single element of a dynamic range
in the WHERE part of the SQL statement instead of typing every single one
AND, AND ?

I tried the following but obviously didnt work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Doesn't a multiple cell range solve your problem?

"LuisE" wrote:

Thanks, Joel
My problem is not defining the range as a variable or making it to auto
expand;
it is querying the content of every single cell in the range.



"Joel" wrote:

You can set a range variable
with sheets("sheet2")
LastColumn = .cells(1,Columns.Count).end(xltoleft).column
LastRow = .cells(Rows.count,"A").end(xlup).row
set QRange = .range(.cells(1,"A"),.cells(LastRow,LastColumn))
MyTable.MyField=QRange
end with

"LuisE" wrote:


I need to query several records (averaging 2,000) that change every week.
First of all Microsoft query tells me that the SQL statement is too long.
Let's say that I reduce the size of the statement to a workable size if not
other options exist,

What statement can I use to refer to every single element of a dynamic range
in the WHERE part of the SQL statement instead of typing every single one
AND, AND ?

I tried the following but obviously didnt work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

No, here is the code for the query, notice after WHERE it is referring to a
range different from statying every single element of that range ie
WHERE (ECL.LORD= cellA1.value AND ECL.LORD= cellA2.value AND ECL.LORD=
cellA3.value and so on................. that is wath I'm trying to accomplish


With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Cott;", Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT ECL.LCARR, ECL.LDESC, ECL.LORD, ECL.LQSHP, ECL.LUM" &
Chr(13) _
& "" & Chr(10) & "FROM PRDBPCS.PRODF.ECL ECL" & Chr(13) & "" &
Chr(10) _
& "WHERE (ECL.LORD= '" & Qrange & "')" _
& Chr(13) & "" & Chr(10) & "ORDER BY ECL.LORD, ECL.LDESC")

.Name = "Query from Cott_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With