Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
I posted this once already, but I think there was just too much in the
question, so I'm rewording it =P When I use my query to get data from specific cells (using parameters) and put it into a subquery, I get a "Type mismatch" error: INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS [ITEMSUM], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE (INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM But when I use the parameter by itself it runs just fine: WHERE (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "' ) Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
When I do a query (Design View) in Access using only one Table with
criteria and look at the SQL, the keyword is WHERE. However, when I do a query in Access using joined Tables with criteria and GROUP BY and look at the SQL, the keyword is HAVING instead of WHERE. Also, the GROUP BY clause comes before the HAVING clause. It might be easier to do the query in the db app, e.g. Access, in order to get a firmer grasp on the SQL. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
On Apr 5, 9:55 am, "merjet" wrote:
When I do a query (Design View) in Access using only one Table with criteria and look at the SQL, the keyword is WHERE. However, when I do a query in Access using joined Tables with criteria and GROUP BY and look at the SQL, the keyword is HAVING instead of WHERE. Also, the GROUP BY clause comes before the HAVING clause. It might be easier to do the query in the db app, e.g. Access, in order to get a firmer grasp on the SQL. Hth, Merjet Hi Merjet, Thanks for replying! I am using Excel VBA to process the SQL query, and it's in Excel VBA that I'm getting the 'Type Mismatch' error in the query. I did the suggested HAVING instead of WHERE, and putting it after the GROUP BY clause, but still got the error. At a glance, does it LOOK like anything is off? I've triple-checked my tables and columns, and everything looks like it should come out just fine! INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM INVOICES INNER JOIN X_INVOIC ON (X_INVOIC.ORDER_NO = INVOICES.ORDER_NO) GROUP BY X_INVOIC.ITEM_CODE HAVING (INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "' )) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
Decided I should also post the query that I hae. I put the subquery in
a module by itself, and it ran just fine. It just doesn't want to run as a subquery. 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) " _ , _ -----Here's where I get the "Type Mismatch" "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 & "') GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM0 " _ ----- , _ "INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM], sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC GROUP BY X_INVOIC.ITEM_CODE) InvoiceSUM ON ITEMS.ITEMNO = InvoiceSUM.ITEMSUM " _ , _ "INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2], sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO GROUP BY X_PO.ITEM_CODE) POSUM ON ITEMS.ITEMNO = POSUM.ITEMSUM2 " _ , _ "INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI], SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO GROUP BY X_PO.ITEM_CODE) 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.DATE_FLD 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 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
Sorry, but I can't help anymore on this. The only db app I have is
Access, and it doesn't accept sql's in the form you are trying, e.g. starting with INNER JOIN. Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum with Parameters in a Subquery
On Apr 9, 6:36 pm, "merjet" wrote:
Sorry, but I can't help anymore on this. The only db app I have is Access, and it doesn't accept sql's in the form you are trying, e.g. starting with INNER JOIN. Merjet No worries, Merjet. I've tried another route anyhow...I think I'm exceeding the character limit for an array formula (har har!), so I'm trying other ways to write out my formula! Thanks for all of your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003, Microsoft Query - reference to subquery alias | Excel Discussion (Misc queries) | |||
Subquery after Left Join | Excel Programming | |||
Too few parameters | Excel Programming | |||
Subquery | Excel Worksheet Functions |