Query with multiple selections from a listbox PROBLEM!!

[kidrobot]

I'm using the following code to set multiple values from a LIST BOX as parameter. The problem is when I select more than 1 value the query returns nothing at all, but if i select 1 value the query will work. All my code seems correct, but can a query take criteria from a text box like I have done in my code? For example the text box will populate: 201 or 202 or 236 or 245 or 389 ... and no results in my query will be return. The criteria in my object query is [forms]![form]![txtParameter]

Any help would be great!!!

Private Sub btnSub_Click()
Dim ctl As Control
Dim varItm As Variant
Dim stItem As String

Set ctl = Me!lstTran

For Each varItm In ctl.ItemsSelected
stString = stString & IIf(stString = "", "", " or ") & ctl.Column(0, varItm)
Next varItm

Me.txtParameter = stString
DoCmd.OpenQuery "zzzfinaltrial"
End Sub

 

[Dennisk]

No it can't. A Parameter is for comparing one field only.
If you need to compare to a number of values you require the IN() Function which goes into the criteria cell like this

IN("UK",USA","SA")

you could build up a query in code and append it to the query collection - that should work but its not something I have done.

 

[kidrobot]

No it can't. A Parameter is for comparing one field only.
If you need to compare to a number of values you require the IN() Function which goes into the criteria cell like this

LIKE ("UK",USA","SA")

you could build up a query in code and append it to the query collection - that should work but its not something I have done.

I tried to look up the In() Function in goolge but I had no success, care to elaborate?

You said a parameter is for comparing one field, but when I manually type the criteria: 201 or 202 or 236 .... The query works just fine, so could you tell me what you're trying to say when you said that that type of parameter isn't possible?

 

[Jibbadiah]

Here is some code from my multi-selection list box. If the field is text you will need the code below... if it is numeric you should change the -3 to a -1... this will remove the trailing comma (whereas the -3 removes trailing comma and quotes).

'Select Contents from Listbox

strParams = ""
intFilterFlag = 1

For Each i In ListParam.ItemsSelected
strParams = strParams & ListParam.ItemData(i) & "','"
Next i

If Len(strParams) > 0 Then
strParams = Left(strParams, Len(strParams) - 3) ' remove trailing comma and quotes
intFilterFlag = intFilterFlag + 1
End If

'Define Filter/Where requirements for Recordset
Select Case intFilterFlag
Case Is = 1
strWhereClause = "(YourField) IS NOT NULL" 'nothing was selected, return all records
Case Is = 2
strWhereClause = "(YourField) in ('" & strParams & "')"
End Select

DoCmd.OpenReport "rptReport", acViewPreview, , strWhereClause 'open report based on selection

' Step through the code and check the values to see if you can work out what is happening... then you can adjust it to your requirements.

 

[kidrobot]

Thanks a lot. This may be a dumb question but what do I put for (Your Field). Also, eventually I will be using this query I'm trying to run in a Report but as for now how can I make this code just open the query. I used docmd.openquery will that work with this code?

Also, I am assuming this is linked to an object query? If so what do I put as criteria for the field in the query... [forms]![formname]![ListParam]? Is this correct?

 

[Jibbadiah]

The yourfield is whatever the listbox is referring to. What field do you restrict your query to when you use the values from the list box? (i.e. 201 or 202 or 236). You can use the strWhereClause in a recordset, a report, a querydefinition or a d function... like dcount.

Try experimenting with something like the following...

Dim qdfTemp As QueryDef

Set qdfTemp = .CreateQueryDef("qryTestQuery", "SELECT * FROM Tablename WHERE " & strWhereClause & ";")

Docmd.openquery "qryTestQuery"

.QueryDefs.Delete "qdfTemp"

 

[kidrobot]

I wanted to find a way to do this without SQL code. I also tried that code above and I get an error at .CreateQueryDef .. "invalid or unqualified reference"

 

[kidrobot]

Jibb, I have tried it with a report as you have. And when I try to run the code I get the following error.

Run-tim error 3075

syntax error in string in query expression '((Trn) in ('203','204','205','))'.

 

[Jibbadiah]

You are getting closer...

For an IN statement you want your query to look like this.

'if Field is text

Select * from Tablename where Field in ('203', '204', '205')

Or

'if Field is numeric

Select * from Tablename where Field in (203, 204, 205)

So your error shows that you have extra text at the end of your query.
This section...

If Len(strParams) > 0 Then
strParams = Left(strParams, Len(strParams) - 3) ' remove trailing comma and quotes
End If

... should trap and remove the extra text.

Try stepping through the code to see exactly what is happening at each stage. Put a watch on at the top of the code before you press your command button. Then press F8 to step through each line of code and read the values. If you want to check the syntax that is created in your strWhereClause, add this code below the Case Statement...

msgbox strWhereClause

... and then check it against the query syntax that I have mentioned above.

Alternatively post your db and I can have a look.

J.

 

[kidrobot]

so would my sql look like the following...

strSQL = "SELECT qry2FINAL.[Trn Orgn Dt], qry2FINAL.[City Name 19], qry2FINAL.OS1, qry2FINAL.Trn, qry2FINAL.[Train Id], qry2FINAL.Block, qry2FINAL.[SumOfOutside Lgth Ft], qry2FINAL.[Car Total], qry2FINAL.[SumOfCountOfFlat Nr] " _
& "FROM qry2FINAL where Trn in (" & strString & ")";

 

[Jibbadiah]

Looks like you may have it... if you want to be sure paste the following in a module and run it... you should end up with a query called qryTestQuery with the results that you want... then tailor it to suit your requirements.

Public Function test()

Dim strString As String
Dim qdfTemp As QueryDef

strString = "'203', '204', '205'"

Set qdfTemp = CurrentDb.CreateQueryDef("qryTestQuery", "SELECT * FROM qry2FINAL WHERE Trn in (" & strString & ");")

DoCmd.OpenQuery "qryTestQuery"
'Or alternatively output a report that references the query

'Delete query and free memory
CurrentDb.QueryDefs.Delete "qryTestQuery"
Set qdfTemp = Nothing

End Function

 

[kidrobot]

How can I make this...

Set ctl = Me!lstTrains

For Each varItm In ctl.ItemsSelected
stString = stString & IIf(stString = "", "", " or ") & ctl.Column(0, varItm)
Next varItm

say this... "'203', '204', '205'" .... I've tried to minipulate it but I have been unsuccessfull. Currently it gives me .... 203 or 204 or 205 ... I tried to add & " ' " to the beginning and change or to ' but it didn't format correctly. Thanks for all the help so far!

 

[kidrobot]

I've tried

stString = " ' " & stString & " ' " & "," & ctl.Column(0, varItm)

which looks correct but when the code runs the string looks nothing like it's supposed to.

 

[pbaldy]

This example help?

    For Each varItem In ctl.ItemsSelected
      strIN = strIN & "'" & ctl.ItemData(varItem) & "', " 
    Next varItem

 

[kidrobot]

Thanks!! Although I found some other code that works as well...

For i = 0 To lstTrains.ListCount - 1
If lstTrains.Selected(i) Then
If lstTrains.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstTrains.Column(0, i) & "',"
End If
Next i

Thanks for you help!

 

[pbaldy]

From an efficiency standpoint, I'd rather cycle through just the selected items, than all items and testing each to see if it's selected. Both methods will work however.

 

[boblarson]

From an efficiency standpoint, I'd rather cycle through just the selected items, than all items and testing each to see if it's selected. Both methods will work however.

Amen to that! It may not make a difference with very small lists, but if you have medium to large lists to iterate through it doesn't make sense to go through all of them. It really doesn't make sense to do it anyway, but you can choose to do what works for you. I like Paul's code a lot as it is cleaner and very clear and easy to maintain as well.