Sum with Parameters in a Subquery

[Diana]

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?

 

[merjet]

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

 

[Diana]

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 & "' ))

 

[Diana]

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(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) " _
, _

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

 

[merjet]

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

 

[Diana]

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!