Access: String to Words

Was used for a SQL SERVER 2000 full text keyword search, ie client enters a sequence of words:

Function RemoveSpecialCharacters(target As String) As String

Dim PositionInTarget As Integer
Dim CurrentCharAsciiCode As Integer
Dim LastCharacterWasASpace As Boolean ' So we don't repeat spaces in our output

target = Trim(target)
LastCharacterWasASpace = False

If Len(target) <> 0 Then
For PositionInTarget = 1 To Len(target)
CurrentCharAsciiCode = Asc((Mid(target, PositionInTarget, 1)))
If (CurrentCharAsciiCode >= 65 And CurrentCharAsciiCode <= 90) Or (CurrentCharAsciiCode >= 97 And CurrentCharAsciiCode <= 122) Then
RemoveSpecialCharacters = RemoveSpecialCharacters & Chr(CurrentCharAsciiCode)
LastCharacterWasASpace = False
Else
If CurrentCharAsciiCode <> 96 Then ' single quote gets dropped
If Not LastCharacterWasASpace Then
RemoveSpecialCharacters = RemoveSpecialCharacters & " "
LastCharacterWasASpace = True
End If
End If
End If
Next PositionInTarget

End If

End Function


Private Function MakeKeyWordSearchSQLClause(Keywords As String) As String

' Expects only a trim'd string with a-z, A-Z and spaces
' Assembles where Contains.. sql clauses for Notes & Treatment fields

Dim PositionInTarget As Integer
Dim CurrentCharAsciiCode As Integer
Dim CurrentWord As String
Dim CountWords As Integer

Keywords = Trim(Keywords)
CountWords = 0

If Len(Keywords) <> 0 Then

MakeKeyWordSearchSQLClause = " AND ("

For PositionInTarget = 1 To Len(Keywords)
CurrentCharAsciiCode = Asc((Mid(Keywords, PositionInTarget, 1)))
If CurrentCharAsciiCode = 32 Then ' space so we have a word end..
' Block repeated for last word below
CountWords = CountWords + 1
If CountWords > 1 Then
MakeKeyWordSearchSQLClause = MakeKeyWordSearchSQLClause & " AND "
End If
MakeKeyWordSearchSQLClause = MakeKeyWordSearchSQLClause & MakeKeyWordSearchSQLWordClause(CurrentWord)
' End repeated block
CurrentWord = ""
Else
CurrentWord = CurrentWord & Chr(CurrentCharAsciiCode)
End If

Next PositionInTarget

' Block repeated for previous words above
CountWords = CountWords + 1
If CountWords > 1 Then
MakeKeyWordSearchSQLClause = MakeKeyWordSearchSQLClause & " AND "
End If
MakeKeyWordSearchSQLClause = MakeKeyWordSearchSQLClause & MakeKeyWordSearchSQLWordClause(CurrentWord)
' End repeated block

MakeKeyWordSearchSQLClause = MakeKeyWordSearchSQLClause & " )"

End If

End Function

Private Function MakeKeyWordSearchSQLWordClause(Word) As String

MakeKeyWordSearchSQLWordClause = " (CONTAINS(Notes, '" & Word & "') OR CONTAINS(Treatment, '" & Word & "'))"

End Function