Okay, here's the final stretch to the home-run: I have a VBA Module
that has an extremely long SQL query array. Normally this wouldn't be
too much of an issue, but unfortunately, I cannot make any more "line
continuations", and still have more SQL query information to add to
the Array (yikes).

So what can I do with this array to make it "fit" all of the extra
information...or do I have to reformat my entire query (I don't mind
doing that one bit if I knew how)? I've heard that you can construct
the SQL into strings and then "join" them together, but each time I
try, I just get a bunch of errors and end up going back to my original
query format.

Could I possibly get a hand with this, or is it just too much to
handle? =P

I'll post what I have with some of the SQL Array deleted (shown
by ...) so it won't look too terribly disgusting (and yes, each of the
line continuations has just under 255 characters each).

Thanks in advance!

~~~~~~~~~~~~~~~~~~~~

Sub Connect3()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))

cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

With sh.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network= DBMSS" _
), Array("OCN")), Destination:=sh.Range("A8"))

.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ... FROM X_STK_AREA LEFT OUTER
JOIN ITEMS ON " _
, _
"(X_STK_AREA.ITEM_NO = ITEMS.ITEMNO) ... LEFT OUTER JOIN
(SELECT X_INVOIC.ITEM_CODE AS " _
, _
"[ITEMSUM0], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO =
INVOICES.DOC_NO) ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) ... " _
, _
"OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], ..." _
, _
"(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') ... " _
, _
"(X_STK_AREA.AREA_CODE='MAIN') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) ORDER BY ITEMS.ITEMNO")

.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Next
End Sub

Diana,
You can build the string first:

DIM SQLStr As String
SQLStr = "SELECT DISTINCT ITEMS.ITEMNO, ..."
SQLStr = SQLStr & " FROM X_STK_AREA LEFT OUTER..."
SQLStr = SQLStr & " JOIN ITEMS ON "
.... etc

NickHK

"Diana" wrote in message
oups.com...
Okay, here's the final stretch to the home-run: I have a VBA Module
that has an extremely long SQL query array. Normally this wouldn't be
too much of an issue, but unfortunately, I cannot make any more "line
continuations", and still have more SQL query information to add to
the Array (yikes).

So what can I do with this array to make it "fit" all of the extra
information...or do I have to reformat my entire query (I don't mind
doing that one bit if I knew how)? I've heard that you can construct
the SQL into strings and then "join" them together, but each time I
try, I just get a bunch of errors and end up going back to my original
query format.

Could I possibly get a hand with this, or is it just too much to
handle? =P

I'll post what I have with some of the SQL Array deleted (shown
by ...) so it won't look too terribly disgusting (and yes, each of the
line continuations has just under 255 characters each).

Thanks in advance!

~~~~~~~~~~~~~~~~~~~~

Sub Connect3()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))

cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

With sh.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network= DBMSS" _
), Array("OCN")), Destination:=sh.Range("A8"))

.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ... FROM X_STK_AREA LEFT OUTER
JOIN ITEMS ON " _
, _
"(X_STK_AREA.ITEM_NO = ITEMS.ITEMNO) ... LEFT OUTER JOIN
(SELECT X_INVOIC.ITEM_CODE AS " _
, _
"[ITEMSUM0], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO =
INVOICES.DOC_NO) ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) ... " _
, _
"OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], ..." _
, _
"(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') ... " _
, _
"(X_STK_AREA.AREA_CODE='MAIN') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) ORDER BY ITEMS.ITEMNO")

.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Next
End Sub

On Apr 18, 8:00 pm, "NickHK" wrote:
Diana,
You can build the string first:

DIM SQLStr As String
SQLStr = "SELECT DISTINCT ITEMS.ITEMNO, ..."
SQLStr = SQLStr & " FROM X_STK_AREA LEFT OUTER..."
SQLStr = SQLStr & " JOIN ITEMS ON "
... etc

NickHK

"Diana" wrote in message

oups.com...



Okay, here's the final stretch to the home-run: I have a VBA Module
that has an extremely long SQL query array. Normally this wouldn't be
too much of an issue, but unfortunately, I cannot make any more "line
continuations", and still have more SQL query information to add to
the Array (yikes).

So what can I do with this array to make it "fit" all of the extra
information...or do I have to reformat my entire query (I don't mind
doing that one bit if I knew how)? I've heard that you can construct
the SQL into strings and then "join" them together, but each time I
try, I just get a bunch of errors and end up going back to my original
query format.

Could I possibly get a hand with this, or is it just too much to
handle? =P

I'll post what I have with some of the SQL Array deleted (shown
by ...) so it won't look too terribly disgusting (and yes, each of the
line continuations has just under 255 characters each).

Thanks in advance!

~~~~~~~~~~~~~~~~~~~~

Sub Connect3()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))

cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

With sh.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network= DBMSS" _
), Array("OCN")), Destination:=sh.Range("A8"))

.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ... FROM X_STK_AREA LEFT OUTER
JOIN ITEMS ON " _
, _
"(X_STK_AREA.ITEM_NO = ITEMS.ITEMNO) ... LEFT OUTER JOIN
(SELECT X_INVOIC.ITEM_CODE AS " _
, _
"[ITEMSUM0], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO =
INVOICES.DOC_NO) ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) ... " _
, _
"OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], ..." _
, _
"(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') ... " _
, _
"(X_STK_AREA.AREA_CODE='MAIN') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) ORDER BY ITEMS.ITEMNO")

.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Next
End Sub- Hide quoted text -

- Show quoted text -

Ahhh beautiful, thank you Nick!

Now how to I put them all together (I'm a bit of a VBA "newbie", as
you would call it). Would I use a Join(SqlStr) to mash them all into
one, or would keeping them all in separate strings do the trick?

On Apr 19, 8:16 am, Diana wrote:
On Apr 18, 8:00 pm, "NickHK" wrote:





Diana,
You can build the string first:

DIM SQLStr As String
SQLStr = "SELECT DISTINCT ITEMS.ITEMNO, ..."
SQLStr = SQLStr & " FROM X_STK_AREA LEFT OUTER..."
SQLStr = SQLStr & " JOIN ITEMS ON "
... etc

NickHK

"Diana" wrote in message

roups.com...

Okay, here's the final stretch to the home-run: I have a VBA Module
that has an extremely long SQL query array. Normally this wouldn't be
too much of an issue, but unfortunately, I cannot make any more "line
continuations", and still have more SQL query information to add to
the Array (yikes).

So what can I do with this array to make it "fit" all of the extra
information...or do I have to reformat my entire query (I don't mind
doing that one bit if I knew how)? I've heard that you can construct
the SQL into strings and then "join" them together, but each time I
try, I just get a bunch of errors and end up going back to my original
query format.

Could I possibly get a hand with this, or is it just too much to
handle? =P

I'll post what I have with some of the SQL Array deleted (shown
by ...) so it won't look too terribly disgusting (and yes, each of the
line continuations has just under 255 characters each).

Thanks in advance!

~~~~~~~~~~~~~~~~~~~~

Sub Connect3()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))

cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

With sh.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network= DBMSS" _
), Array("OCN")), Destination:=sh.Range("A8"))

.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ... FROM X_STK_AREA LEFT OUTER
JOIN ITEMS ON " _
, _
"(X_STK_AREA.ITEM_NO = ITEMS.ITEMNO) ... LEFT OUTER JOIN
(SELECT X_INVOIC.ITEM_CODE AS " _
, _
"[ITEMSUM0], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO =
INVOICES.DOC_NO) ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) ... " _
, _
"OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], ..." _
, _
"(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') ... " _
, _
"(X_STK_AREA.AREA_CODE='MAIN') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) ORDER BY ITEMS.ITEMNO")

.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Next
End Sub- Hide quoted text -

- Show quoted text -

Ahhh beautiful, thank you Nick!

Now how to I put them all together (I'm a bit of a VBA "newbie", as
you would call it). Would I use a Join(SqlStr) to mash them all into
one, or would keeping them all in separate strings do the trick?- Hide quoted text -

- Show quoted text -

Oh! I was able to get the VBA to recognize each statement, so there
are no errors in the VBA. However, when I try to run the Sub, nothing
happens. =) Something else I'm missing, perhaps?

Here is the working query:

Sub ConnectRedo()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet
Dim oQt As QueryTable
Dim sConn As String
Dim ssql As String

Set ws = Sheets(Array("Inventory Analyzer", "Last 30 Days", "Last 90
Days", "Last 180 Days", "Last 360 Days"))

For Each sh In ws
cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

ssql = "SELECT DISTINCT ITEMS.ITEMNO, ..."
ssql = ssql & "FROM X_STK_AREA LEFT OUTER JOIN ITEMS ON ..."
ssql = ssql & "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS ... "
ssql = ssql & "INNER JOIN INVOICES ON (INVOICES.ORDER_NO = ..."
ssql = ssql & "WHERE ((ITEMS.ACTIVE='T') AND
(ITEMS.INVENTORED='T') ... "
ssql = ssql & "AND (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' ..."

sConn = "ODBC;DSN=Everest;Description=Everest data;UID=;PWD=;
APP=Microsoft Office 2003; DATABASE=EVEREST_VGI;Network=DBMSS"

With sh.QueryTables.Add(Connection:=sConn, _
Destination:=sh.Range("A8"), _
SQL:=sqlstr)
End With
Next
End Sub

On Apr 19, 8:34 am, Diana wrote:
On Apr 19, 8:16 am, Diana wrote:





On Apr 18, 8:00 pm, "NickHK" wrote:

Diana,
You can build the string first:

DIM SQLStr As String
SQLStr = "SELECT DISTINCT ITEMS.ITEMNO, ..."
SQLStr = SQLStr & " FROM X_STK_AREA LEFT OUTER..."
SQLStr = SQLStr & " JOIN ITEMS ON "
... etc

NickHK

"Diana" wrote in message

roups.com...

Okay, here's the final stretch to the home-run: I have a VBA Module
that has an extremely long SQL query array. Normally this wouldn't be
too much of an issue, but unfortunately, I cannot make any more "line
continuations", and still have more SQL query information to add to
the Array (yikes).

So what can I do with this array to make it "fit" all of the extra
information...or do I have to reformat my entire query (I don't mind
doing that one bit if I knew how)? I've heard that you can construct
the SQL into strings and then "join" them together, but each time I
try, I just get a bunch of errors and end up going back to my original
query format.

Could I possibly get a hand with this, or is it just too much to
handle? =P

I'll post what I have with some of the SQL Array deleted (shown
by ...) so it won't look too terribly disgusting (and yes, each of the
line continuations has just under 255 characters each).

Thanks in advance!

~~~~~~~~~~~~~~~~~~~~

Sub Connect3()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))

cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

With sh.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network= DBMSS" _
), Array("OCN")), Destination:=sh.Range("A8"))

.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ... FROM X_STK_AREA LEFT OUTER
JOIN ITEMS ON " _
, _
"(X_STK_AREA.ITEM_NO = ITEMS.ITEMNO) ... LEFT OUTER JOIN
(SELECT X_INVOIC.ITEM_CODE AS " _
, _
"[ITEMSUM0], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO =
INVOICES.DOC_NO) ... " _
, _
"(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) ... " _
, _
"JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) ... " _
, _
"OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], ..." _
, _
"(PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "') ... " _
, _
"(X_STK_AREA.AREA_CODE='MAIN') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) ORDER BY ITEMS.ITEMNO")

.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Next
End Sub- Hide quoted text -

- Show quoted text -

Ahhh beautiful, thank you Nick!

Now how to I put them all together (I'm a bit of a VBA "newbie", as
you would call it). Would I use a Join(SqlStr) to mash them all into
one, or would keeping them all in separate strings do the trick?- Hide quoted text -

- Show quoted text -

Oh! I was able to get the VBA to recognize each statement, so there
are no errors in the VBA. However, when I try to run the Sub, nothing
happens. =) Something else I'm missing, perhaps?

Here is the working query:

Sub ConnectRedo()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet
Dim oQt As QueryTable
Dim sConn As String
Dim ssql As String

Set ws = Sheets(Array("Inventory Analyzer", "Last 30 Days", "Last 90
Days", "Last 180 Days", "Last 360 Days"))

For Each sh In ws
cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

ssql = "SELECT DISTINCT ITEMS.ITEMNO, ..."
ssql = ssql & "FROM X_STK_AREA LEFT OUTER JOIN ITEMS ON ..."
ssql = ssql & "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS ... "
ssql = ssql & "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS ... "
ssql = ssql & "INNER JOIN INVOICES ON (INVOICES.ORDER_NO = ..."
ssql = ssql & "WHERE ((ITEMS.ACTIVE='T') AND
(ITEMS.INVENTORED='T') ... "
ssql = ssql & "AND (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' ..."

sConn = "ODBC;DSN=Everest;Description=Everest data;UID=;PWD=;
APP=Microsoft Office 2003; DATABASE=EVEREST_VGI;Network=DBMSS"

With sh.QueryTables.Add(Connection:=sConn, _
Destination:=sh.Range("A8"), _
SQL:=sqlstr)
End With
Next
End Sub- Hide quoted text -

- Show quoted text -


Heh...found it. Lame error: I made SQL:=sqlstr instead of SQL:=ssql

Works now...thank you so much, Nick!