|  | entering multiple parameters in a parameter box? | | |
| | | bruno |  |
|  Posted: Thu Sep 18, 2008 2:39 pm Post subject: entering multiple parameters in a parameter box? |  |
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 | |
| | Posted: Thu Sep 18, 2008 3:02 pm Post subject: Re: entering multiple parameters in a parameter box? | |
Here's a crazy (and inefficient) solution:
PARAMETERS WotNum Text ( 255 );
SELECT tblMember.[MemberID]
FROM tblMember
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [MemberID] & ",*";
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"bruno" <bruno@discussions.microsoft.com> wrote in message
news:8E89BCC2-71DC-4A1B-91DD-E15146890BB4@microsoft.com...
| Quote: | 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? |
|
| |
| | | Ken Sheridan | |
| | Posted: Thu Sep 18, 2008 3:10 pm Post subject: RE: entering multiple parameters in a parameter box? | |
| |  | |
You'll find a couple of methods for doing this at:
LINK
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
"bruno" wrote:
| Quote: | 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? |
|
| |
|
|
