Replacing comma with OR 1

[bitech]

I am using Access 97, and I need to allow my users to type in multiple keywords in a textbox and have the database, replace the comma with the word OR.

That way my SQL statement will read the textbox field and find any records in the database that have matching keywords.

Example:

user types in:
dog,cat,mouse,house

Through code it should change it to read,
*dog* or *cat* or *mouse* or *house*

If you can help please do.

 

[n2ckb]

try this:
UserStr = "dog,cat,mouse,house"
NewStr = Replace(UserStr,chr(44),"* or *&quot
NewStr = "*" & NewStr & "*"

The last line adds the asterisks at the start and end of the string.

Hope this helps.

N

 

[bitech]

In Access97, the Replace Function isn't available. Somehow I need to write my own, but I don't know how.

 

[Kleinen]

This should work in Access 97


Dim strText As String
Dim intLastPos As Integer, intPos As Integer

intPos = InStr(1, strText, Chr(44), vbTextCompare)

Do While intPos <> 0
strText = Left(strText, intPos - 1) & &quot;* or *&quot; & Mid(strText, intPos + 1, Len(strText) - intPos)
intLastPos = intPos
intPos = InStr(intLastPos + 1, strText, Chr(44))
Loop

strText = &quot;*&quot; & strText & &quot;*&quot;

 

[bitech]

Can you explain that code to me.

 

[Kleinen]

Dim strText As String
Dim intLastPos As Integer, intPos As Integer

strText = &quot;cats,dogs,house,mouse&quot;
'The InStr function returns an integer stating the start of the string
'you where looking for in another string
'First argument: the place in the string where to start looking
'Second argument: string expression being searched
'Third argument: string expression sought
'Fourth argument: type of string comparison
'If the string expression sought is not found the the result is 0
intPos = InStr(1, strText, Chr(44), vbTextCompare)

Do While intPos <> 0
'Rebuild the string without the comma you have just found using the InStr function
strText = Left(strText, intPos - 1) & &quot;* or *&quot; & Mid(strText, intPos + 1, Len(strText) - intPos)
intLastPos = intPos
intPos = InStr(intLastPos + 1, strText, Chr(44))
Loop

strText = &quot;*&quot; & strText & &quot;*&quot;

Hope this helps B-)

 

[Quehay]

Check out the MS Knowledge Base article on creating a &quot;Parameter IN Statement&quot;: Q210530

This offers a couple of methods, and it's the very situation you're looking at here!

 

[bustell]

Here is a simple function that works exactly as the Replace function in VB:

Public Function Replace(sString As String _
, sFind As String _
, sReplace As String _
, Optional iCompare As Long = vbBinaryCompare) As String

Dim iStart As Integer, iLength As Integer
iStart = InStr(1, sString, sFind, iCompare)
Do While iStart > 0
sString = Left(sString, iStart - 1) _
& sReplace & Mid(sString, iStart + Len(sFind) _
, Len(sString) - iStart - Len(sFind) + 1)
iStart = InStr(iStart, sString, sFind, iCompare)
Loop
Replace = sString
End Function

Pat B