Unanswered: Grab comma delimited vals and wrap in quotes for sql IN function?
I have a user form where they can filter the results by the Offer and by the Major. Since there can be several majors, i would like for them to be able to input 1 or more separated by commas: A,U,W. The issue is that when building the SQL string, each of those characters needs to be wrapped in ticks to work with the IN clause: ...IN('A','U','W'). Suggestions?
offer = Me.txtOffer
major = Me.txtMajor
strSQL = "Select * from tblMain WHERE [offer] = '" & offer & "' AND LEFT([Item Number],1) IN(" & major & ")"
Here's a possibility. You'll still have to test for txtOffer and txtMajor being empty, though.
Const c_SQL As String = "SELECT * FROM tblMain WHERE offer = '@O' AND LEFT([Item Number],1) IN ( @M );"
Dim strSQL As String
Dim strMajor As String
Dim varMajor As Variant
Dim i As Integer
varMajor = Split(Me.txtMajor.Value, ",")
For i = 0 To UBound(varMajor)
If Len(strMajor) > 0 Then strMajor = strMajor & ", "
strMajor = strMajor & "'" & varMajor(i) & "'"
strSQL = Replace(Replace(c_SQL, "@O", Me.TxtOffer.Value), "@M", strMajor)