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

Question

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

What happens if you remove the references to the input fields and replace them with hardcoded values like in your SQL Query? If that returns the proper results, then you know the issue is with the values getting passed by one of your input fields. — Jeff Rosenberg
I can remove the references & I'll still get the recordset, only without the two records that should also be included. I've also double & triple checked the date/time. Ran independent queries and found that there is nothing in the two missing records that would disqualify them. — user2116602
Sorry, can't upload the db. It's way too large and has sensitive data in it. I've posted about all I'm going to be able to without getting in trouble. I was thinking it might be a problem with the form itself, so I created a new form from the ground up & it's still returning all I want except that it's omitting those 2 records. HUGE thanks again to all for the help! — user2116602

Tim Lentine Tim Lentine · Accepted Answer · 2013-02-27T20:14:41

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.

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

1 Answers