CODEWITHSUNDEEP
sqlms-access
user2116602
user2116602

Reputation: 3

MS Access form (VBA) query & regular query giving different results

I have essentially the same query in MS Access via the VBA code & just a saved query. It's basically the same query, but the VBA code is missing two records that qualify & should be included. The saved query is returning the same dataset AND the two 'missing' records that the VBA is not returning.

Saved Query SQL: 

SELECT ID.Company, CU.GroupCode, CU.CustID, CU.Name, ST.ShipToNum, ST.Name, IH.InvoiceDate, IH.InvoiceNum, ID.ProdCode, ID.PartNum
FROM ((((PUB_InvcDtl AS ID INNER JOIN PUB_InvcHead AS IH ON (ID.CustNum = IH.CustNum) AND (ID.InvoiceNum = IH.InvoiceNum) AND (ID.Company = IH.Company)) LEFT JOIN PUB_Part AS PT ON (ID.Company = PT.Company) AND (ID.PartNum = PT.PartNum)) LEFT JOIN PUB_ShipTo AS ST ON (ID.CustNum = ST.CustNum) AND (ID.ShipToNum = ST.ShipToNum) AND (ID.Company = ST.Company)) LEFT JOIN V_Customer AS CU ON (ID.Company = CU.Company) AND (ID.CustNum = CU.CustNum)) LEFT JOIN ProdCodeCommRate AS PCC ON ID.ProdCode = PCC.ProdCode
WHERE (((ID.Company)="BTN") AND ((IH.InvoiceDate) Between #1/1/2013# And #1/31/2013#) AND ((ID.ProdCode) In ('2100','2110','2200','2210','2300','2400','2405','2500','2600','2701','2702','2709','2710','2800','2901','2902','2903')) AND ((ID.PartNum)<>''));

VBA Query: 

"SELECT ID.Company, CU.GroupCode, CU.CustID, CU.Name AS CustName, ST.ShipToNum, ST.Name AS ShipToName, IH.InvoiceDate, IH.InvoiceNum, ID.InvoiceLine, ID.PartNum, " & _
        "ID.ProdCode, PCC.CommRate, ID.PricePerCode, PT.PartDescription, ID.IUM, PT.CostMethod, ID.UnitPrice, ID.OurShipQty, ID.ExtPrice, ID.Discount, [ExtPrice]-[Discount] AS NetPrice, " & _
        "ID.LbrUnitCost, ID.BurUnitCost, ID.MtlUnitCost, ID.SubUnitCost, [LbrUnitCost]+[BurUnitCost]+[MtlUnitCost]+[SubUnitCost] AS TotUnitCost, " & _
        "([LbrUnitCost]+[BurUnitCost]+[MtlUnitCost]+[SubUnitCost])*[OurShipQty] AS ExtTotCost, IH.OpenInvoice, getSalesRep([IH].[SalesRepList],1,ID.Company) AS SalesRep1, ID.RepRate1, " & _
        "ID.RepSplit1, getSalesRep([IH].[SalesRepList],2,ID.Company) AS SalesRep2, ID.RepRate2, ID.RepSplit2, getSalesRep([IH].[SalesRepList],3,ID.Company) AS SalesRep3, " & _
        "ID.RepRate3, ID.RepSplit3 " & _
        "FROM (((((PUB_InvcDtl AS ID " & _
        "INNER JOIN PUB_InvcHead AS IH ON (ID.CustNum = IH.CustNum) AND (ID.InvoiceNum = IH.InvoiceNum) AND (ID.Company = IH.Company)) " & _
        "LEFT JOIN PUB_Part AS PT ON (ID.Company = PT.Company) AND (ID.PartNum = PT.PartNum))" & _
        "LEFT JOIN PUB_ShipTo AS ST ON (ID.CustNum = ST.CustNum) AND (ID.ShipToNum = ST.ShipToNum) AND (ID.Company = ST.Company))" & _
        "LEFT JOIN V_Customer AS CU ON (ID.Company = CU.Company) AND (ID.CustNum = CU.CustNum))" & _
        "LEFT JOIN ProdCodeCommRate AS PCC ON ID.ProdCode = PCC.ProdCode)" & _
        "WHERE ((ID.Company)=[Forms]![frmSalesMgnAnalysis]![cboComp]) AND ((ID.PartNum)<>'') AND " & _
        "((IH.InvoiceDate) Between [Forms]![frmSalesMgnAnalysis]![dtStart] And [Forms]![frmSalesMgnAnalysis]![dtEnd]) AND " & _
        "((ID.ProdCode) IN ('2100','2110','2200','2210','2300','2400','2405','2500','2600','2701','2702','2709','2710','2800','2901','2902','2903'))" & _
        "ORDER BY ID.ProdCode, IH.InvoiceDate, CU.Name;"

The form is using input fields for Company, Start & End Dates, & ProdCodes. The ProdCodes are in a listbox on the form that includes the ProdCode, Desc, & Company (which are in a table in the VBA side). This VBA code returns all the records I need EXCEPT the 2 missing records. Won't work whether I join the ProdCode VBA table above, select it distinctly in the WHERE using a WHERE ID.ProdCode IN (Select distinct...), etc.

Ideas? Thanks in advance!!!

Upvotes: 0

Views: 718

Answers (1)

Tim Lentine
Tim Lentine

Reputation: 7862

Your WHERE clauses are not identical (in that the parenthesis are not in the same places between both queries). I suspect you might be having a problem specifically with the ID.PartNum<>'' portion of the clause.

You might try setting a breakpoint in your VBA code and get the actual interpreted output of the VBA query (including parameter values). Copy that SQL statement into a new query window (SQL View) and then look at the designer to see how Access is interpreting the statement.

You could also try matching up the where clause in the VBA query to match the same order of the arguments and number of parenthesis, but it might be easier to use the designer first to prove \ disprove my hypothesis before mucking around with the VBA query.

Often with problems like these it is a matter of trial and error to take statements away and gradually add them back to find exactly where the problem is occurring, but the parenthesis usage is where I'd start investigating.

Upvotes: 1

Related Questions