In() criteria statement & ALL in listbox

Hi everyone -

A quick Question:

1 - is there a wildcard (beside the * ) that can be used to retrieve records in a In() statement in a query's criteria?

I have a listbox that is set to multi-select. The listbox hold items that can be selected and then these items are built into a string and inserted into a SQL statement using the In() property.

Is there a way to insert something in the In() property that will at as a wildcard and pull all of the records? Bascially - I want the user to be able to select any number of items in the listbox to query on... if the user whats to use all of the items then (instead of having to select them all <around120>) they could select ALL as one of the option.

I have inserted ALL in the list but I can't figure out what I need to put in the In() statement to retrieve everything in the list...?

Please help me get started on this if you can. I did a lot of searching on this in this forum and my Bible but I can't figure it out.

Thanks in advance,
Kevin

Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()
Select Case Me.optVal
Case Is = 1
strSQL = "INSERT INTO etc



'create the IN string by looping thru the listbox
For I = 0 To List0.ListCount - 1

If List0.Selected(I) Then
If List0.Column(0, I) = "All" Then
flgAll = True
End If
strIN = strIN & Chr(34) & List0.Column(2, I) & Chr(34) & ","
End If
Next I

'create the WHERE string, stripping off the last comma of the IN string
strWhere = "WHERE [LastName] In (" & left((strIN), Len(strIN) - 1) & " ) And ([tblLetters].[fldLtrPK] In(" & Me![txtNum] & "))"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

DBEngine(0)(0).Execute strSQL, dbFailOnError

Hi Rich,

First - Thanks for the reply and the offer to help.

Second - Is there a way to do this without stripping of the WHERE statement and with that the In() function?

The reason I ask is that I didn't explain myself thoroughly enough in that I really have 3 listboxes on the form (I was trying to simplify it as much as possible) Each list box hold information used to build the query. Each of the three listboxes have the item ALL added to them. So... Stripping off the WHERE clause doesn't work because you need it for the other listboxes.

One possible example could look like this:

List 1 -- User selects item 2,3
List 2 -- User Selects ALL
List 3 -- User Selects items 4,5,9

The Where clause has 3 conditional In() statements so removing the whole where clause won't work...

Any idea how I can work around this? I cen send the db if that would help...

Thanks Again Rich,
Kev

I got this problem resolved!

Thanks for the help with this Rich.

Kevin