Function buildMatchCountSQL(user_input As String) As String
  ' produce a SQL select list item of:
  '  , CASE WHEN PO.Pname LIKE '*abc*' THEN 1 ELSE 0 END
  '  + CASE WHEN PO.Pname LIKE '*def*' THEN 1 ELSE 0 END
  '  + CASE WHEN PO.Pname LIKE '*xyz*' THEN 1 ELSE 0 END
  '  AS matches
  ' Parse the user input into an array of match words
  '  replace commas with spaces so there is only 1 type of delimiter
  Dim aString As String
  aString = Replace(user_input, ",", " ")
  '  put all words in a temporary array
  Dim TempArray() As String
  TempArray() = Split(aString, " ")
  '  build SQL predicates ignoring any empty strings
  Dim intCount As Integer
  intCount = 0
  For Each Word In TempArray
    If Word <> "" Then
      ' remove leading/trailing blanks, convert word to uppercase,
      ' wrap word in quotes and wildcard characters
      Word = "'*" & Trim(UCase(Word)) & "*'"
      ' start selection with comma, join with plus sign
      If intCount = 0 Then
        buildMatchCountSQL = ", "
      Else
        buildMatchCountSQL = buildMatchCountSQL & " + "
      End If
      buildMatchCountSQL = buildMatchCountSQL & "CASE WHEN PO.Pname LIKE " & Word
      buildMatchCountSQL = buildMatchCountSQL & " THEN 1 ELSE 0 END "
      intCount = intCount + 1
    End If
  Next
  ' add the column name (unless there are no matches)
  If buildMatchCountSQL <> "" Then
    buildMatchCountSQL = buildMatchCountSQL & " AS matches"
  End If
End Function
 
Function buildMatchSQL(user_input As String) As String
  ' produce a SQL predicate of:
  '  AND ( PO.Pname LIKE '*abc*'
  '     OR PO.Pname LIKE '*def*'
  '     OR PO.Pname LIKE '*xyz*'
  '      )
  ' Parse the user input into an array of match words
  '  replace commas with spaces so there is only 1 type of delimiter
  Dim aString As String
  aString = Replace(user_input, ",", " ")
  '  put all words in a temporary array
  Dim TempArray() As String
  TempArray() = Split(aString, " ")
  '  build SQL predicates ignoring any empty strings
  Dim intCount As Integer
  intCount = 0
  For Each Word In TempArray
    If Word <> "" Then
      ' remove leading/trailing blanks, convert word to uppercase,
      ' wrap word in quotes and wildcard characters
      Word = "'*" & Trim(UCase(Word)) & "*'"
      ' wrap predicate list in parentheses, join with OR
      If intCount = 0 Then
        buildMatchSQL = " AND ( "
      Else
        buildMatchSQL = buildMatchSQL & " OR "
      End If
      buildMatchSQL = buildMatchSQL & "PO.Pname LIKE " & Word
      intCount = intCount + 1
    End If
  Next
  ' add the closing parenthesis (unless there are no predicates)
  If buildMatchSQL <> "" Then
    buildMatchSQL = buildMatchSQL & " )"
  End If
End Function
 
Function buildSQL(user_input As String) As String
  buildSQL = "SELECT PO.PCode, PO.Pname, PPLO.Price,PO.status, PPLO.Efct_Date, PO.Hlink,PO.website"
  ' add match count to the SQL select column list
  ' (if there are no words to match, this will be an empty string)
  buildSQL = buildSQL & buildMatchCountSQL(user_input)
  buildSQL = buildSQL & " FROM Pmast_Others PO"
  buildSQL = buildSQL & " INNER JOIN Product_Price_List_Others PPLO"
  buildSQL = buildSQL & " ON PO.[PCode] = PPLO.[PCode]"
  buildSQL = buildSQL & " WHERE PO.status is NULL"
  ' add conditions to the SQL predicate list
  ' (if there are no words to match, this will be an empty string)
  buildSQL = buildSQL & buildMatchSQL(user_input) 
End Function
 
Function TEST() as String
  Dim user_input As String
  Dim strSQL As String
  Dim newLine As String
  newLine = Chr(13)
 
  user_input = InputBox("Enter terms separated by commas or spaces", "User Input", "")
  strSQL = buildSQL(user_input)
  Dim x As String
  If user_input = "" Then
    user_input = "(empty string)"
  End If
  x = "'" & user_input & "' => " & newLine & newLine & strSQL
  x = InputBox(x, "SQL Query String Result", x)
  TEST = strSQL
End Function