How to set up IN(list) as a parameter

[Steve Marshall]

Hello all,

I need to set up a query where part of the WHERE clause is an IN(list)
construct. The list will consist of one or more values (up to 3 or 4)
selected by the user. I therefore would like the "list" part to be a
parameter to the query, so I can plug in the set of values before I run
the query. But I can't get it to work.

In the query I have the criterion for the field as In([ItemList]).
ItemList is set up as a query parameter of type Text. The field is a
text field. The value I am putting into ItemList in my code is a
string with the field values separated by commas. I have tried them
without any quotes, with single quotes (around each individual value),
and with double quotes (ditto). But nothing works - it never gets a
match. I don't get any complaint about the syntax in any of these
cases.

Can this be done? It seems to me like it should be quite
straightforward. But I seem to recall trying to do this once before and
never cracking it. Any assistance most welcome.

 

[Van T. Dinh]

Not quite the In operator but you can use the technique in The Access Web
article:

http://www.mvps.org/access/forms/frm0007.htm

 

[Jamie Collins]

I need to set up a query where part of the WHERE clause is an IN(list)
construct.

See this thread:

http://groups-beta.google.com/group...s.queries/browse_frm/thread/963e9d5b1194037b/

I prefer the approach that uses InputStrings and Parmlist, which is a
direct copy of a Joe Celko standard post with which I'm familiar.

Jamie.

--

 

[Guest]

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function