* Force explicit variable declaration (best practices)
* Use Regexp object to do the cleaning and parsing
* Retain Bill's Quote() function
* Use Join() function to add the " AND " strings
* Return a valid Where clause if nothing parsed
Option ExplicitSub Q_28926419() Dim rng2, strsql rng2 = BuildWhere("FLAPPER ""234"" #$@@ buzzkill") strsql = strsql & rng2 & ";" Debug.Print strsqlEnd SubFunction BuildWhere(ByVal cD As String) Const lmlk = "WwgCore.RICHTEXT Like " Dim cArrSql() As String Static oRE As Object Dim oMatches As Object Dim oM As Object Dim lngSM As Long If oRE Is Nothing Then Set oRE = CreateObject("vbscript.regexp") oRE.Global = True oRE.Pattern = "[0-9A-Za-z]+" End If If oRE.test(cD) Then Set oMatches = oRE.Execute(cD) ReDim cArrSql(1 To oMatches.Count) lngSM = 1 For Each oM In oMatches cArrSql(lngSM) = lmlk & Quote("%" & oM.Value & "%") lngSM = lngSM + 1 Next Else BuildWhere = "WHERE 0=1" End If BuildWhere = "WHERE " & Join(cArrSql, " AND ")End FunctionFunction Quote(ByVal strText As String) Quote = Chr(34) & strText & Chr(34)End Function
~bp