Hi everyone!
I am attempting to use a parameter form to create the criteria in a query behind my report. The form contains several fields and 6 multi-select list boxes. The list boxes fill their respected text field properly. In my query, I reference the text fields in the criteria like this:
Full SQL looks like this:
If I only have one selection it works fine, and if I paste the actual text into the query it works fine. For example: "Chris Jones" or "Jeremy Smith". But with more than one salesperson (or other fields) selected, the query will not pull any records when I reference the form control. I keep trying the same thing over and over expecting different results, but it's not happening! Any thoughts why? I have verified there is data that matches my criteria. Thank you in advance for any assistance you can give me.
I am attempting to use a parameter form to create the criteria in a query behind my report. The form contains several fields and 6 multi-select list boxes. The list boxes fill their respected text field properly. In my query, I reference the text fields in the criteria like this:
Code:
[Forms]![FReportParams]![TXTSalespeople]
Code:
SELECT DISTINCT Persons.PersonID, Projects.ProjectID, Projects.ProjectNo, Projects.CreateDate, Projects.BidDate, Projects.ProjectName, ProjectItems.ItemBid, ProjectItems.MFGID, ProductGrps.ProductGroup, MFGs.MFG, Bidders.Bidder, BidStatus.BidStatus, ProjectItems.TOPersonID, Projects.ProjectNotes, Projects.QuoteCompleted, Projects.FollowUpDate, ProjectItems.Person, Projects.EstCloseDate, ProjectItems.ItemBid, BidStatus.BidStatusID
FROM Persons RIGHT JOIN (Bidders INNER JOIN (MFGs INNER JOIN (BidStatus INNER JOIN (ProductGrps INNER JOIN ((Projects INNER JOIN ProjectItems ON Projects.ProjectID = ProjectItems.ProjectID) INNER JOIN ProjectBidders ON Projects.ProjectID = ProjectBidders.ProjectID) ON ProductGrps.ProductGrpID = ProjectItems.ProductGrpID) ON BidStatus.BidStatusID = ProjectItems.BidStatusID) ON MFGs.MFGID = ProjectItems.MFGID) ON Bidders.BidderID = ProjectBidders.BidderID) ON Persons.PersonID = ProjectItems.TOPersonID
WHERE (((MFGs.MFG)=[Forms]![FReportParams]![TXTmfgs]) AND ((ProjectItems.Person)=[Forms]![FReportParams]![TXTSalespeople]));
If I only have one selection it works fine, and if I paste the actual text into the query it works fine. For example: "Chris Jones" or "Jeremy Smith". But with more than one salesperson (or other fields) selected, the query will not pull any records when I reference the form control. I keep trying the same thing over and over expecting different results, but it's not happening! Any thoughts why? I have verified there is data that matches my criteria. Thank you in advance for any assistance you can give me.