Query criteria from form

[qqp]

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:

[Forms]![FReportParams]![TXTSalespeople]

Full SQL looks like this:

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.

 

[Andrzejek]

>more than one salesperson (or other fields) selected
How do you select "more than one salesperson"?

In your Select I see:[tt]
...
AND ((ProjectItems.Person)[highlight #FCE94F]=[/highlight][Forms]![FReportParams]![TXTSalespeople]));[/tt]
which looks to me you pick one person from a textbox [tt]TXTSalespeople[/tt]

Unless your [tt]TXTSalespeople[/tt] contains multiple salespeople, then you can try:
[tt]
...
AND ((ProjectItems.Person) [blue][highlight #FCE94F]IN ([/highlight][/blue][Forms]![FReportParams]![TXTSalespeople][blue][highlight #FCE94F])[/highlight][/blue]));[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[qqp]

The list boxes allow for multiple selections, and I have a "Select All" button for each listbox too.

The text box shows the selections like this: John Doe or Steve Smith. At first I had the IN statement appended to the chosen items. I didn't have any luck with that either. I just tried it again with 2 salespeople selected, and got no results. I put it directly into the query criteria. Is that where it should have gone?

Thank you Andy for your time!

 

[Andrzejek]

Your statement will look like:[tt]
AND ProjectItems.Person = John Doe or Steve Smith[/tt] -- no good :-(

What you want is:[tt]
AND ProjectItems.Person IN (John Doe or Steve Smith)[/tt]

but that will not work, because your syntax should be either:[tt]
AND ProjectItems.Person IN ('John Doe', 'Steve Smith')[/tt]
or (double quotes)[tt]
AND ProjectItems.Person IN ("John Doe", "Steve Smith")[/tt]

Instead of full names, you should be using ID's instead. IMHO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[qqp]

I just tried using the Salesperson ID (number) to no avail. If I enter it directly in the query (IN(8,50) I get accurate results, if it's in the text box on the form, I get no data. Even though it's exactly the same.

Thanks again Andy!

 

[combo]

You can find this faq701-4432 useful, also an old Allen Browne tip.

combo

 

[qqp]

Thanks for this combo! I'm getting a syntax error. When I debug.print I get: SELECT * FROM masterquery WHERE topersonid IN (5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 19, 20, 23, 24, 28, 32, 38, 41, 44, 45, 50, 52). If I use this directly in the query it gives me the correct data.

Can you see anything I'm missing? Also, "topersonid" is the name of the field in the query/table. Am I using it correctly?

Here is the code in full now:

DoCmd.SetWarnings False
Dim ctlList
Dim sSql As String

Set ctlList = Me.Salespeople
Set ctlList2 = Me.ProdGrp
Set ctlList3 = Me.MFGS
Set ctlList4 = Me.Bidders
Set ctlList5 = Me.status




   'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strwhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

sSql = "SELECT * FROM masterquery WHERE topersonid IN ("

For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next

sSql = Left(sSql, Len(sSql) - 2) & ")" 'Remove Last comma and single quote and add closing bracket

DoCmd.Openreport Forms![FMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, , sSql


DoCmd.SetWarnings False

Forms("freportparams").SetFocus
    DoCmd.Minimize
    DoCmd.SetWarnings True
    
End Sub

 

[Andrzejek]

If it works for you, then it is correct.

>anything I'm missing?
[tt]Option Explicit[/tt] at the top of your code.

You may also simplify your code by:

...
Dim Lmnt As integer
...
With Me.Salespeople
   For Each Lmnt In .itemsselected
      sSql = sSql & .ItemData(Lmnt) & ","
   Next
Rnf With
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[qqp]

But it' not working for me. I am getting a syntax error at this line:
DoCmd.Openreport Forms![FMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, , sSql

Thank you!

 

[qqp]

Well this works: (note, I removed a comma between acviewpreview and ssql.

sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket

DoCmd.Openreport Forms![fMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, sSql

Thank you all for your time and suggestions! You're all ROCKSTARS in my book! I'll probably be back soon.