Multi-criteria query

[mobear]

Seems like this question comes up a lot, but most of the circumstances are more complex than mine. I have a basic table with a parameter query that prompts for a "Student name." If I only need to search for data relating to "Sally" this setup is fine. However, I can't figure out how to search for 2 students (data on Sally and Joe). I have no problem if I amend the query myself using AND or OR, but I can't seem to get the query to work when I have a user prompt.

Suggestions are greatly appreciated,
mobear

 

[WildHare]

First off, Prompt boxes embedded in queries are notoriously icky. They don't handle input like
"Sally or Joe or Fred or Dwayne or Earnest"

So if you want the ability to have 'multiple' parameters, you'll probably need to rig up a form with a control from which you can PASS the value or value(s) selected.

You'll probably need to build up a SQL clause, and modify the WHERE statement to include your selection(s) from a list box using the ItemsSelected collection.

Most books on Access development should have an example of how to do this, if you can't find anything post back and I'll see if I can whip something up really quick and dirty-like...

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[WildHare]

Here - Just found this on Dev Ashish's web site:


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL to get rid of
' the Or EMPID at the end..
strSQL=left$(strSQL,len(strSQL)-12)) Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com