Quering a changing range SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LuisE - 24 Sep 2007 15:26 GMT

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 didn’t work because it reads it as a
single item
"WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Reply to this Message

Joel - 24 Sep 2007 16:44 GMT

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

> 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.
[quoted text clipped - 8 lines]
> single item
> "WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Reply to this Message

LuisE - 24 Sep 2007 17:04 GMT

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.

> You can set a range variable
> with sheets("sheet2")
[quoted text clipped - 16 lines]
> > single item
> > "WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Reply to this Message

Joel - 24 Sep 2007 17:14 GMT

Doesn't a multiple cell range solve your problem?

> Thanks, Joel
> My problem is not defining the range as a variable or making it to auto
[quoted text clipped - 21 lines]
> > > single item
> > > "WHERE (MyTable.MyField= ' & Sheet2.Range("A1").CurrentRegion & "')"

Reply to this Message

LuisE - 24 Sep 2007 17:46 GMT

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

Reply to this Message