ListBox as criteria for query

[martinmike2]

Hello,

I am sorry to ask this question when it has alreayd been asked several
times before, but I am having issues figuering out the code to
accomplish this.

my code is:
Private Sub Command2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim rst As DAO.Recordset
Set frm = Forms!frmAMDsel
Set ctl = frm!LstWC
strSQL = "WHERE qryAMDSEAsub.WC ="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem)
Next varItem

'Trim the end of strSQL
'strSQL = (Left$(strSQL, Len(strSQL) - 12))
DoCmd.OpenReport "rptAMD_SeaDuty", acViewPreview, , strSQL

End Sub

Now, when i click to command button i get:
Run-Time error (3075): Syntax Error (missing operator) in query
expression.

Am I just being stupid with my SQL statement? Do I need to provide
the full query in the reports where property?

 

[Douglas J. Steele]

If you've got a multiselect list box, you can't use =, you need to use IN
(val1, val2, val3...). As well, the criteria for the OpenReport statement
doesn't include the word WHERE.

Private Sub Command2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim rst As DAO.Recordset

Set frm = Forms!frmAMDsel
Set ctl = frm!LstWC

If ctl.ItemsSelected.Count > 0 Then
strSQL = "WC IN ("

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

'Trim the end of strSQL
strSQL = (Left$(strSQL, Len(strSQL) - 2)) & ")"
End If

DoCmd.OpenReport "rptAMD_SeaDuty", acViewPreview, , strSQL

End Sub

 

[martinmike2]

hmm,

now i'm getting a data type mismatch error.

 

[martinmike2]

the report itself runs off a query that has a subquery in it. The WC
field is located in the subquery, is this wrong?

 

[martinmike2]

nevermind, I figuered it out. I just had to add a ' inside the
parenthases.

 

[martinmike2]

hmm..... maybe not. The report wont open with multiple selections.
If I select multiple values the report only opens with the first one
instead of opening with all of them.

The data type of WC is text because not all of the values in WC are
numbers, there are several that are all text.

How do I modify the code to recognize text instead of numbers?

 

[Douglas J. Steele]

Private Sub Command2_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim rst As DAO.Recordset

Set frm = Forms!frmAMDsel
Set ctl = frm!LstWC

If ctl.ItemsSelected.Count > 0 Then
strSQL = "WC IN ("

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

'Trim the end of strSQL
strSQL = (Left$(strSQL, Len(strSQL) - 2)) & ")"
End If

DoCmd.OpenReport "rptAMD_SeaDuty", acViewPreview, , strSQL

End Sub

Exagerated for clarity, the line that adds to strSQL is

strSQL = strSQL & " ' " & ctl.ItemData(varItem) & " ', "