Subquery Confusion

D

Diana

Okay, this is getting messy (and a bit frustrating =P)!

I need to get this module to work...I am preparing a macro to use SQL
to gather data and spit it out on a worksheet. Everything works
beautifully as their own little modules, but when I want to Inner Join
everything as subqueries, I get the confusing 'Error 13: Type
Mismatch'.
My first thought was that I am trying to match apples to oranges.
However, this is not the case: all of the WHERE Object = Object are
varchar (15) (etc) and are the same data. So I don't have a problem
there.
Then I got this crazy idea that an Array can only contain a maximum
number of characters (correct me if I'm wrong), but have no idea how
to use other modules inside of this module (if that would even work,
considering it's a full SQL query...).
Then I decide that maybe I'm completely wrong with my query, so I pull
it out of Excel VBA and spit it into Microsoft SQL Server Management
Studio Express, and Voila! the query works -perfect-...

Does anything look funky? Am I missing something obvious? Is this
query too much for VBA to handle? Am I referencing the parameters too
many times? Can I even reference a parameter in a subquery? *pulls out
hair*

I appreciate any help whatsoever (from "do this" to "don't do that" to
"why on earth...?")

Thanks in advance, and sorry about this mess!

===Mess starts here:

Sub Connect2()

Dim cellValue1 As String
Dim cellValue2 As String

cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, " _
, _
"InvoiceItemSum.Invoice_Sum, X_STK_AREA.Q_STK,
InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2, ITEMS.AVG_COST, ITEMS.AVG_SP " _
, _
"FROM X_STK_AREA INNER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO) INNER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO) INNER
JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) " _
, _

==='Error 13: Type Mismatch' starts here: (If I remove any part of
this Inner Join...from the "WHERE" to the "FROM" to the "INNER
JOIN"...it works...too many characters??

"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM0],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.ORDER_NO) WHERE
(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" & cellValue2
& "')) InvoiceItemSum ON ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM0 " _
, _

===Type Mismatch also calls this section, same story as above:

"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (INVOICES.DOC_NO = X_INVOIC.ORDER_NO) WHERE (INVOICES.ORDER_DATE
BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "')) InvoiceSUM ON
ITEMS.ITEMNO = InvoiceSUM.ITEMSUM " _
, _

===Type Mismatch calls this section too!:

"INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) POSUM ON ITEMS.ITEMNO =
POSUM.ITEMSUM2 " _
, _

===And another Type Mismatch section (are we excited yet?):

"INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON (PO.DOC_NO
= X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND
'" & cellValue2 & "')) QTYREC ON ITEMS.ITEMNO = QTYREC.QTYRECI " _
, _
"WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) " _
, _
"AND (PO.ORDER_DATE BETWEEN '01/03/2006' AND '04/03/2007' ))
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
End Sub

===Mess ends here (party time!)
 
U

urkec

You could try and replace cellValue1 and cellValue2 with literal values.

Also, you could Debug.Print CommandText to see what it looks like.


--
urkec


Diana said:
Okay, this is getting messy (and a bit frustrating =P)!

I need to get this module to work...I am preparing a macro to use SQL
to gather data and spit it out on a worksheet. Everything works
beautifully as their own little modules, but when I want to Inner Join
everything as subqueries, I get the confusing 'Error 13: Type
Mismatch'.
My first thought was that I am trying to match apples to oranges.
However, this is not the case: all of the WHERE Object = Object are
varchar (15) (etc) and are the same data. So I don't have a problem
there.
Then I got this crazy idea that an Array can only contain a maximum
number of characters (correct me if I'm wrong), but have no idea how
to use other modules inside of this module (if that would even work,
considering it's a full SQL query...).
Then I decide that maybe I'm completely wrong with my query, so I pull
it out of Excel VBA and spit it into Microsoft SQL Server Management
Studio Express, and Voila! the query works -perfect-...

Does anything look funky? Am I missing something obvious? Is this
query too much for VBA to handle? Am I referencing the parameters too
many times? Can I even reference a parameter in a subquery? *pulls out
hair*

I appreciate any help whatsoever (from "do this" to "don't do that" to
"why on earth...?")

Thanks in advance, and sorry about this mess!

===Mess starts here:

Sub Connect2()

Dim cellValue1 As String
Dim cellValue2 As String

cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, " _
, _
"InvoiceItemSum.Invoice_Sum, X_STK_AREA.Q_STK,
InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2, ITEMS.AVG_COST, ITEMS.AVG_SP " _
, _
"FROM X_STK_AREA INNER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO) INNER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO) INNER
JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) " _
, _

==='Error 13: Type Mismatch' starts here: (If I remove any part of
this Inner Join...from the "WHERE" to the "FROM" to the "INNER
JOIN"...it works...too many characters??

"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM0],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.ORDER_NO) WHERE
(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" & cellValue2
& "')) InvoiceItemSum ON ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM0 " _
, _

===Type Mismatch also calls this section, same story as above:

"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (INVOICES.DOC_NO = X_INVOIC.ORDER_NO) WHERE (INVOICES.ORDER_DATE
BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "')) InvoiceSUM ON
ITEMS.ITEMNO = InvoiceSUM.ITEMSUM " _
, _

===Type Mismatch calls this section too!:

"INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) POSUM ON ITEMS.ITEMNO =
POSUM.ITEMSUM2 " _
, _

===And another Type Mismatch section (are we excited yet?):

"INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON (PO.DOC_NO
= X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND
'" & cellValue2 & "')) QTYREC ON ITEMS.ITEMNO = QTYREC.QTYRECI " _
, _
"WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) " _
, _
"AND (PO.ORDER_DATE BETWEEN '01/03/2006' AND '04/03/2007' ))
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
End Sub

===Mess ends here (party time!)
 
D

Diana

You could try and replace cellValue1 and cellValue2 with literal values.

Also, you could Debug.Print CommandText to see what it looks like.

--
urkec



Diana said:
Okay, this is getting messy (and a bit frustrating =P)!
I need to get this module to work...I am preparing a macro to use SQL
to gather data and spit it out on a worksheet. Everything works
beautifully as their own little modules, but when I want to Inner Join
everything as subqueries, I get the confusing 'Error 13: Type
Mismatch'.
My first thought was that I am trying to match apples to oranges.
However, this is not the case: all of the WHERE Object = Object are
varchar (15) (etc) and are the same data. So I don't have a problem
there.
Then I got this crazy idea that an Array can only contain a maximum
number of characters (correct me if I'm wrong), but have no idea how
to use other modules inside of this module (if that would even work,
considering it's a full SQL query...).
Then I decide that maybe I'm completely wrong with my query, so I pull
it out of Excel VBA and spit it into Microsoft SQL Server Management
Studio Express, and Voila! the query works -perfect-...
Does anything look funky? Am I missing something obvious? Is this
query too much for VBA to handle? Am I referencing the parameters too
many times? Can I even reference a parameter in a subquery? *pulls out
hair*
I appreciate any help whatsoever (from "do this" to "don't do that" to
"why on earth...?")
Thanks in advance, and sorry about this mess!
===Mess starts here:
Sub Connect2()
Dim cellValue1 As String
Dim cellValue2 As String
cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, " _
, _
"InvoiceItemSum.Invoice_Sum, X_STK_AREA.Q_STK,
InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2, ITEMS.AVG_COST, ITEMS.AVG_SP " _
, _
"FROM X_STK_AREA INNER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO) INNER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO) INNER
JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) " _
, _
==='Error 13: Type Mismatch' starts here: (If I remove any part of
this Inner Join...from the "WHERE" to the "FROM" to the "INNER
JOIN"...it works...too many characters??
"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM0],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.ORDER_NO) WHERE
(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" & cellValue2
& "')) InvoiceItemSum ON ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM0 " _
, _
===Type Mismatch also calls this section, same story as above:
"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (INVOICES.DOC_NO = X_INVOIC.ORDER_NO) WHERE (INVOICES.ORDER_DATE
BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "')) InvoiceSUM ON
ITEMS.ITEMNO = InvoiceSUM.ITEMSUM " _
, _
===Type Mismatch calls this section too!:
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "')) POSUM ON ITEMS.ITEMNO =
POSUM.ITEMSUM2 " _
, _
===And another Type Mismatch section (are we excited yet?):
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON (PO.DOC_NO
= X_PO.ORDER_NO) WHERE (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND
'" & cellValue2 & "')) QTYREC ON ITEMS.ITEMNO = QTYREC.QTYRECI " _
, _
"WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) " _
, _
"AND (PO.ORDER_DATE BETWEEN '01/03/2006' AND '04/03/2007' ))
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
End Sub
===Mess ends here (party time!)- Hide quoted text -

- Show quoted text -

Heh..well I was able to fix it myself. See, the reason for the Type
Mismatch was nothing other than I had more than 255 characters on each
line of my array. Once I made some more line continuations, the macro
runs like a charm. FYI for anyone else having this issue!! If you have
more than 255 characters per line in your SQL query array, you'll get
screamed at by VBA!!

But thank you for your reply urkec, much appreciated for reading
through all of my garbage!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top