×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query criteria from form

Query criteria from form

Query criteria from form

(OP)
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:

CODE -->

[Forms]![FReportParams]![TXTSalespeople] 
Full SQL looks like this:

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.

RE: Query criteria from form

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

In your Select I see:
...
AND ((ProjectItems.Person)=[Forms]![FReportParams]![TXTSalespeople]));

which looks to me you pick one person from a textbox TXTSalespeople

Unless your TXTSalespeople contains multiple salespeople, then you can try:

...
AND ((ProjectItems.Person) IN ([Forms]![FReportParams]![TXTSalespeople])));

---- Andy

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

RE: Query criteria from form

(OP)
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!

RE: Query criteria from form

Your statement will look like:
AND ProjectItems.Person = John Doe or Steve Smith
-- no good sad

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


but that will not work, because your syntax should be either:
AND ProjectItems.Person IN ('John Doe', 'Steve Smith')

or (double quotes)
AND ProjectItems.Person IN ("John Doe", "Steve Smith")


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

---- Andy

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

RE: Query criteria from form

(OP)
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!

RE: Query criteria from form

(OP)
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:

CODE -->

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 

RE: Query criteria from form

If it works for you, then it is correct. smile

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

You may also simplify your code by:

CODE

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

RE: Query criteria from form

(OP)
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!

RE: Query criteria from form

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

CODE -->

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. :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close