Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subquery Confusion
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 he Sub Connect2() Dim cellValue1 As String Dim cellValue2 As String cellValue1 = Range("B3").Value cellValue2 = Range("B4").Value With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "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 he (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!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003, Microsoft Query - reference to subquery alias | Excel Discussion (Misc queries) | |||
Sum with Parameters in a Subquery | Excel Programming | |||
Subquery after Left Join | Excel Programming | |||
Subquery | Excel Worksheet Functions |