entering multiple parameters in a parameter box?

[bruno]

I have created a parameter query where it asks to search by member id. How
can I build the parameter expression so that more than one member id can be
entered and searched for at once? I can only enter one member id to search at
a time. How can I enter multiple member id's in one search?

 

[Allen Browne]

Here's a crazy (and inefficient) solution:

PARAMETERS WotNum Text ( 255 );
SELECT tblMember.[MemberID]
FROM tblMember
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [MemberID] & ",*";

 

[Ken Sheridan]

You'll find a couple of methods for doing this at:


http://support.microsoft.com/kb/100131/en-us


The second method is more reliable as the first will also find a value which
is a substring of another value, e.g. if you include ID 1234 in the value
list you'll also find 1,2,3 4,12, 23, 34, 123 and 234.

However, a better method is to use a multi-select list box on a dialogue
form and, with a button on the form, open a form or report bound to a query
(with no parameter). Here's an example which opens a report for multiple
customers:

For the list box's RowSource property:

SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

For other properties:

Name: lstCustomers
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, CustomerID, column, so only the
name shows)
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the report, called rptCustomers in this
example, with the following in its Click event procedure:

Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCustomers

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & "," &
ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[CustomerID] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "[rptCustomers]", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No customers selected", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England