Access VBA query error 3122 (1 Viewer)

[walrus]

Hello

First post here!

I am using MS Access 2003 VBA. I have a query that worked and did what I wanted...

strSQL = "SELECT tblCombiSS.CustomerID, tblCombiSS.CustomerName, tblCombiSS.UAID, tblMaterials.MaterialIDDW, tblMaterials.Material, tblMaterials.P24BUABR, tblProductClassID.ProductClass, tblProductLineID.ProductLine, TimePeriod.YEAR_FISCAL, TimePeriod.TIME_SID, [MONTH_ABBREVIATION] & "" "" & [YEAR_CALY] AS [Date], Sum(tblSales.TotalNetRevenue) AS SumOfTotalNetRevenue1, Sum(tblSales.QtyInvoiced) AS SumOfQtyInvoiced " & vbCrLf & _
 
"FROM tblUser INNER JOIN (tblProductLineID INNER JOIN (tblProductGroupID INNER JOIN (tblProductFamilyID INNER JOIN (tblProductClassID INNER JOIN (tblBusinessUnitID INNER JOIN (tblBusinessDivisionID INNER JOIN ((tblMaterials INNER JOIN ((tbPlantID INNER JOIN tblSales ON tbPlantID.PlantSID = tblSales.PlantSID) INNER JOIN TimePeriod ON tblSales.TimePeriodSID = TimePeriod.TIME_SID) ON tblMaterials.MaterialSID = tblSales.MaterialSID) INNER JOIN tblCombiSS ON tblSales.CombiSS = tblCombiSS.CombiSS) ON tblBusinessDivisionID.BusinessDivisionID = tblMaterials.BusinessDivisionID) ON tblBusinessUnitID.BusinessUnitID = tblMaterials.BusinessUnitID) ON tblProductClassID.ProductClassID = tblMaterials.ProductClassID) ON tblProductFamilyID.ProductFamilyID = tblMaterials.ProductFamilyID) ON tblProductGroupID.ProductGroupID = tblMaterials.ProductGroupID) ON tblProductLineID.ProductLineID = tblMaterials.ProductLineID) ON tblUser.RepCode = tblCombiSS.KAMID " & vbCrLf & _
 
"GROUP BY tblCombiSS.CustomerID, tblCombiSS.CustomerName, tblCombiSS.UAID, tblMaterials.MaterialIDDW, tblMaterials.Material, tblMaterials.P24BUABR, tblProductClassID.ProductClass, tblProductLineID.ProductLine, TimePeriod.YEAR_FISCAL, TimePeriod.TIME_SID, [MONTH_ABBREVIATION] & "" "" & [YEAR_CALY], tblCombiSS.KAMID " & vbCrLf & _
 
"HAVING (Sum(tblSales.TotalNetRevenue)<>0) AND tblCombiSS.KAMID='UA0';"

...until I tried to add some extra criteria: AND (tbPlantID.PlantID)<>'GB05' to the end of the HAVING statement. It fails with error 3122.

The query runs ok in Access but not in VBA. Can anyone tell me what I have done wrong?

 

[pr2-eugin]

Try this as the Query..

strSQL = "SELECT tblCombiSS.CustomerID, tblCombiSS.CustomerName, tblCombiSS.UAID, tblMaterials.MaterialIDDW, tblMaterials.Material, tblMaterials.P24BUABR, tblProductClassID.ProductClass, tblProductLineID.ProductLine, TimePeriod.YEAR_FISCAL, TimePeriod.TIME_SID, [MONTH_ABBREVIATION] & "" "" & [YEAR_CALY] AS [Date], Sum(tblSales.TotalNetRevenue) AS SumOfTotalNetRevenue1, Sum(tblSales.QtyInvoiced) AS SumOfQtyInvoiced " & vbCrLf & _
"FROM tblUser INNER JOIN (tblProductLineID INNER JOIN (tblProductGroupID INNER JOIN (tblProductFamilyID INNER JOIN (tblProductClassID INNER JOIN (tblBusinessUnitID INNER JOIN (tblBusinessDivisionID INNER JOIN ((tblMaterials INNER JOIN ((tbPlantID INNER JOIN tblSales ON tbPlantID.PlantSID = tblSales.PlantSID) INNER JOIN TimePeriod ON tblSales.TimePeriodSID = TimePeriod.TIME_SID) ON tblMaterials.MaterialSID = tblSales.MaterialSID) INNER JOIN tblCombiSS ON tblSales.CombiSS = tblCombiSS.CombiSS) ON tblBusinessDivisionID.BusinessDivisionID = tblMaterials.BusinessDivisionID) ON tblBusinessUnitID.BusinessUnitID = tblMaterials.BusinessUnitID) ON tblProductClassID.ProductClassID = tblMaterials.ProductClassID) ON tblProductFamilyID.ProductFamilyID = tblMaterials.ProductFamilyID) ON tblProductGroupID.ProductGroupID = tblMaterials.ProductGroupID) ON tblProductLineID.ProductLineID = tblMaterials.ProductLineID) ON tblUser.RepCode = tblCombiSS.KAMID " & vbCrLf & _
"GROUP BY tblCombiSS.CustomerID, tblCombiSS.CustomerName, tblCombiSS.UAID, tblMaterials.MaterialIDDW, tblMaterials.Material, tblMaterials.P24BUABR, tblProductClassID.ProductClass, tblProductLineID.ProductLine, TimePeriod.YEAR_FISCAL, TimePeriod.TIME_SID, [MONTH_ABBREVIATION] & "" "" & [YEAR_CALY], tblCombiSS.KAMID " & vbCrLf & _
"HAVING ((Sum(tblSales.TotalNetRevenue)<>0) AND ((tblCombiSS.KAMID)='UA0') AND ((tbPlantID.PlantID)<>'GB05'));"

Also, Try using Debug.Print for understanding how your Query is build up when using in VBA..

 

[walrus]

debug.print got it - I'd made a mistake in my query design! The problem was in the 'GROUP BY' - I didn't add in tbPlantID.PlantID, so the base query worked without it, but when I added the criteria on plantID I got the error.

Cheers for the direction!