By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,620 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,620 IT Pros & Developers. It's quick & easy.

Combine selections from listbox in VBA

P: 11
Hi - I've adopted some code from font stuff to be able to select multiple rows in a list box to send to a query.

I'm using an Autonumber ID field to identify the records I want from the list.

Can anyone help me with my run-error 3075 on STMT2? Syntax Error(missing operator) in query expression '[qdfMULTI.CustID] in (100',102')'.
Those are the correct items, but when I check my query, it is returning all.


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMULTI_Click()
  2.     Dim ms As DAO.Database
  3.     Dim STMT2 As DAO.QueryDef
  4.     Dim varitem As Variant
  5.     Dim strCriteria As String
  6.     Dim strSQL As String
  7.  
  8.     Set ms = CurrentDb()
  9.     Set STMT2 = ms.QueryDefs("qrySTMTMM")
  10.  
  11.     For Each varitem In Me!lstMM.ItemsSelected
  12.         strCriteria = strCriteria & "," & Me!lstMM.ItemData(varitem) & "'"
  13.     Next varitem
  14.  
  15.     If Len(strCriteria) = 0 Then
  16.         MsgBox "You did not select anything from the list!", vbExclamation, "Nothing to Find!"
  17.     Exit Sub
  18.     End If
  19.  
  20.     strCriteria = Right(strCriteria, Len(strCriteria) - 1)
  21.  
  22.     strSQL = "SELECT * FROM qrySTMTMM " & " WHERE [qdfMULTI.CustID] IN (" & strCriteria & ");"
  23.  
  24.     STMT2.SQL = strSQL
  25.  
  26.     DoCmd.OpenQuery "MMSGSummaryMULTI"
  27.     Set ms = Nothing
  28.     Set STMT2 = Nothing
  29. End Sub
when I hover over the error, I get
STMT2: The correct query and columns show...including CustID
strSQL: strSQL = "SELECT * FROM qrySTMTMM " & " WHERE [qdfMULTI.CustID] IN (100',102');"
Feb 14 '19 #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,284
MC42015,

It depends on if your values are supposed to be numeric or strings. It should look this way for numeric:

Expand|Select|Wrap|Line Numbers
  1. [qdfMulti].[CustID] In (100, 102)
And this way for strings:

Expand|Select|Wrap|Line Numbers
  1. [qdfMulti].[CustID] In (100, 102)
Whatever code you are using to create your criteria string must come up with those values.

Hope this hepps!
Feb 14 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.