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!)
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!)