Query won't accept Multiselect LB Criteria

[JSaumya]

Hello,

I've scoured the dozen archived threads that relate to my challenge, but I still can't quite overcome it.

I have an aggregate query that runs but returns no values.

This is how I've tried to set things up:
A user makes a multiple selection in a list box on a form.
The multiple selection gets strung into an SQL that I feed to a hidden control on the form.
Whatever is in the hidden control then becomes the Where clause that I reference in a Criteria cell in my query.
The query is run when the user presses a command button.

Can anybody tell what's wrong with my code? Thanks very much in advance!

Private Sub cmdTractSelect_Click()
On Error GoTo Err_cmdTractSelect_Click

'Declare variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim qry As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String

'Assign values to the variables
Set db = CurrentDb()
Set tbl = db.TableDefs("City")
Set fld = tbl.Fields("Tract")
Set qry = db.QueryDefs("Param")
Set frm = Forms!frmTractSelect
Set ctl = frm!lstTract
strSQL = "[Tract]="

'Grab the list-box selection and string it into SQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " Or [Tract]="
Next varItem

'Make sure the user selected at least one tract
If Len(strSQL) = 0 Then
MsgBox "You didn't select anything" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

'Trim the SQL
strSQL = Left$(strSQL, Len(strSQL) - 12)

'Insert the SQL into a hidden control that will feed_
'the criteria of the Tract field
txtWhere.Value = strSQL

'Run the query
DoCmd.OpenQuery ("Param")

Exit_cmdTractSelect_Click:
Exit Sub

Err_cmdTractSelect_Click:
MsgBox Err.Description
Resume Exit_cmdTractSelect_Click

End Sub

 

[Pat Hartman]

If the field is text, you need to surround each entry with quotes.

A shorter syntax is to use the In(....) construct.

Where YourField In("abc", "xyzzz", "21xy");

If the values are text, they need to be surrounded by quotes as above. If they are numeric then don't use quotes as below:

Where YourField In(145, 9887, 10104);

Add a breakpoint in your code so you can display the sql string before you use it.

 

[JSaumya]

query not accepting string from multiselect lb

Thanks ever so much for your suggestions. My query is still not returning any values, though.

I changed my code to string the selection from the list box into an IN statement. To do this, I now have strCriteria, which strings the lb selection, and strIn, which puts strCriteria into IN syntax.

My text box txtWhere then accepts the strIn-- after putting in a break point, I found that strCriteria and strIn are coming out ok, with the result that the text box reads txtWhere.value= "IN('2','3','4')"

I have referenced this text box in my query grid criteria cell -- for my field [Tract], the Where criteria is [Forms]![frmTractSelect]![txtWhere]

Here's my reworked code (changes in bold), in case you've got some time to look at it. Thanks again!
Private Sub cmdTractSelect_Click()
On Error GoTo Err_cmdTractSelect_Click

'Declare variables
Dim db As Database
Dim qry As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strIn As String
Dim strCriteria As String

'Assign values to the variables
Set db = CurrentDb()
Set qry = db.QueryDefs("Param")
Set frm = Forms!frmTractSelect
Set ctl = frm!lstTract

'strCriteria: Grab the list-box selection and string it into sql
For Each varItem In ctl.ItemsSelected
strCriteria = strCriteria & ",'" & ctl.ItemData(varItem) & "'"
Next varItem

'Make sure the user selected at least one tract
If Len(strCriteria) = 0 Then
MsgBox "You didn't select anything" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

'Trim the Criteria string to get rid of leading comma
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

'strIn: Create the IN statement
strIn = "IN(" & strCriteria & ")"

'Insert the IN statement into a hidden control that will feed_
'the criteria of the Tract field
txtWhere.Value = strIn

'Run the query
DoCmd.OpenQuery ("Param")

Exit_cmdTractSelect_Click:
Exit Sub

Err_cmdTractSelect_Click:
MsgBox Err.Description
Resume Exit_cmdTractSelect_Click

End Sub

 

[Pat Hartman]

1. You can't pass the "In(...)" as a parameter. You need to build the entire SQL string in VBA.
2. If your field is numeric, the values must not be enclosed in quotes.

 

[JSaumya]

Thanks again.
Still working on it...