Search String Problem

[shyamsundar]

Hi techies,

This is what i tried to search records on a table
****************************************
Private Sub cmdKYGO_Click()
Dim row As Integer
Dim strKWQUERY As String
row = lstCOUNTRY.ListIndex
If row < 0 Then
MsgBox &quot;Please Select Country&quot;
lstCOUNTRY.SetFocus
Exit Sub
ElseIf txtSEARCH.Value = &quot;Enter Search String&quot; Then
MsgBox &quot;Please type your Search String&quot;
txtSEARCH.SetFocus
Exit Sub
End If
Dim strSRCH As String
strSRCH = txtSEARCH.Value
Dim dbHIV As Database
Dim qdfKWSRCH As QueryDef
Set dbHIV = CurrentDb
strKWQUERY = &quot;select * from 1a where [1a.8] like '*&quot; & strSRCH & &quot;*'&quot; _
& &quot; or [1a.11] like '*&quot; & strSRCH & &quot;*'&quot; _
& &quot; or [1a.13] like '*&quot; & strSRCH & &quot;*'&quot; _
& &quot; or [1a.44] like '*&quot; & strSRCH & &quot;*'&quot; _
& &quot; or [1a.46] like '*&quot; & strSRCH & &quot;*'&quot; _
& &quot; order by [1a.1]&quot;

For Each qdfKWSRCH In dbHIV.QueryDefs
If qdfKWSRCH.Name = &quot;KW_SRCH&quot; Then
DoCmd.DeleteObject acQuery, &quot;KW_SRCH&quot;
End If
Next qdfKWSRCH
Set qdfKWSRCH = dbHIV.CreateQueryDef(&quot;KW_SRCH&quot;, strKWQUERY)
dbHIV.QueryDefs.Refresh
DoCmd.OpenForm &quot;KW_SRCH&quot;, acNormal
End Sub

***********************************************
Now, the problem is, the user enters more than one word seperated by comma(, this can be changed to +) to search, the above code will not search as it is looking for the string (SUGAR,MILK) AND NOT SUGAR & MILK seperately. Please any help.

Shyam

Shyam
cwizshyam@yahoo.com

 

[RJFost]

If you're just wanting to limit what the user can enter into a textbox (or whatever) as a target string, try something like this:

Private Sub tbxInput_KeyPress (KeyAscii as Integer)

Select Case KeyAscii
Case 38, 39, 42
'ASCII values for &, ', * characters;
'could be coded as Case Asc(&quot;&&quot, Asc(&quot;'&quot, Asc(&quot;*&quot
KeyAscii = 0 'Changes keycode to 0 (null)
Case Else
End Select

Exit Sub

You can extend the Case (or add others, to form logical groups) to include most any keyboard character you could want.

Myself, I've done this countless times on forms to prevent apostrophes ('), the bane of my existence in Access.

 

[CVigil]

I believe RJFost missed the point of shyamsundar's request. Shyam, you want to allow a user to enter multiple search criteria, for instance &quot;sugar&quot; and &quot;milk&quot;. The syntax you would have them use for such a search is &quot;sugar, milk&quot;, although you would be willing to use the syntax as &quot;sugar + milk&quot; if it makes what you want possible. Am I correct in my understanding of your need?

If so, then I am left with another question. Again assuming a search specifying the terms &quot;sugar&quot; and &quot;milk&quot;, would you A) want your search to return all records containing &quot;sugar&quot; and all records containing &quot;milk&quot;, or would you B) want only all records that contain both terms? This is effectively the difference between using AND or OR in your search criteria (i.e. [records containing &quot;sugar&quot; AND &quot;milk&quot;] or else [records containing &quot;sugar&quot; OR &quot;milk&quot;]).

But what you need is a way to deal with the terms in a string separately. The function InStr is very useful for such purposes. I have used it in the following function, which you will probably have to paste back into the module editor to read:

'************************
Public Function MyStrArray(ByVal strList As String, ByVal strSep As String) As Variant
'- This function accepts a single string which is a list of character-separated values.
'- The character used as a separator is specified for the function in strSep.  Only its
'  first character is used, if it is longer than 1 character.
'- strSep is not included in any of the returned array elements.
'- It does not matter if strList ends in strSep or not; this function handles either case.

Dim NewArray() As String
Dim intPos As Integer 'position of strSep w/in strList
Dim intCount As Integer 'current # of elements in array

    strSep = Left(strSep, 1)
    intPos = InStr(1, strList, strSep, vbBinaryCompare)
    Do Until (intPos = 0)
        ReDim Preserve NewArray(0 To intCount)
        NewArray(intCount) = Left(strList, intPos - 1)
        strList = Right(strList, Len(strList) - intPos) 'drop piece added to array & strSep
        intPos = InStr(1, strList, strSep, vbBinaryCompare) 'check for another strSep
        If (Len(strList) > 0) Then intCount = intCount + 1 'more left, have another element
    Loop
    If (Len(strList) > 0) Then 'if any left, it goes into one more element
        ReDim Preserve NewArray(0 To intCount)
        NewArray(intCount) = strList
    End If
    MyStrArray = NewArray 'Return variant containing array of strings
End Function
'************************

Here is a small Sub I wrote to test my function, and which should demonstrate its use:

'************************
Public Sub TestMyStrArray()
Dim g As Variant, x As Integer
Const SEPARATOR = ","
    g = MyStrArray("Gravy" & SEPARATOR & _
                   "Tower" & SEPARATOR & _
                   "Modulus", _
                   SEPARATOR)
    For x = LBound(g) To UBound(g)
        Debug.Print g(x)
    Next x
End Sub
'************************

The function accepts a string to process, and a string which should be one character used to separate parts of the first string. It returns a variant containing an array of strings, the parts of the &quot;processed string&quot;. In your hypothetical case, if the user enetered &quot;sugar,milk&quot;, then in your code, varVariable = MyStrArray(strSEARCH.text,&quot;,&quot should result in varVariable containing an array; and varVariable(0) should contain &quot;sugar&quot;, and varVariable(1) should contain &quot;milk&quot;. If the user has spaces in the list, you have to deal with that separately. Use the Trim function, either on the returned array, or inside MyStrArray if you always want to get rid of spaces before and after items in lists passed to the function -- whichever you decide is appropriate for your uses.

Okay, with that established, it seems that what you need is a way to create a SQL statement with the search terms passed in. (Once you have that, your form opens using the query you re-create as a RecordSource. Is that correct?)

So loop through the elements of the array, adding to your variable strKWQUERY as you go. You don't even need to know how many elements are in the array.

So again, here is some code which uses the above -- a function and a Sub that tests the function. You should be able to replace the assignment to strKWQUERY with a call to this function:

'************************
Option Compare Database
Option Explicit
Private Function BuildKWQuery(varArray As Variant) As String
'Assumes that array has at least one element.
'
'You must decide if you want the returned records to contain:
'    A) *all* search terms (JOINEXPRESSION = " AND")
'    or
'    B) *at least one* of the search term (Const JOINEXPRESSION = " OR").
Const JOINEXPRESSION = " AND" 'or OR, whichever you want

    Dim intLoop As Integer, intFirst As Integer, intLast As Integer
    Dim strKWQuery As String, strSRCH As String
    
    intFirst = LBound(varArray)
    intLast = UBound(varArray)
    strKWQuery = strKWQuery & _
                 "SELECT * FROM 1a WHERE"
    For intLoop = intFirst To intLast
        strSRCH = varArray(intLoop)
        If (intLoop > intFirst) Then strKWQuery = strKWQuery & JOINEXPRESSION
        strKWQuery = strKWQuery & _
                     " ([1a.8] Like '*" & strSRCH & "*'" _
                     & " or [1a.11] like '*" & strSRCH & "*'" _
                     & " or [1a.13] like '*" & strSRCH & "*'" _
                     & " or [1a.44] like '*" & strSRCH & "*'" _
                     & " or [1a.46] like '*" & strSRCH & "*')"
    Next intLoop
    strKWQuery = strKWQuery & " ORDER BY [1a.1];"
    BuildKWQuery = strKWQuery
End Function
 
Private Sub TestBuildKWQuery()
Dim varArray As Variant, strKWQuery As String

    varArray = MyStrArray("Gravy,Tower,Blue", ",")
    strKWQuery = BuildKWQuery(varArray)
    MsgBox strKWQuery
    Debug.Print strKWQuery 'so I can paste it into a query to see if it runs okay
                           'so I can see if I missed a space or parenthesis or
                           'did something else wrong in the function...
End Sub
'************************

In fact, with these procedures in place,

    strKWQuery = BuildKWQuery(MyStrArray(strSEARCH, ","))

should work for you just fine! Just replace the present assignment line with this one.

Hope this helps! Any questions, just write back . -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[shyamsundar]

Dear CVigil & RJFost,

Thanx very much for your inputs. (Cvigil) excellent logic, I can use this in my future VB projects.

And, I discovered a Split Function in the MSDN CD, just after I posted this message, I am sorry I troubled you. Let me share u the split function
Private Sub cmdKYGO_Click()
If txtSEARCH.Value = &quot;Enter Search String&quot; Then
MsgBox &quot;Please type your Search String&quot;
txtSEARCH.SetFocus
Exit Sub
End If
Dim arrSRCH() As String
Dim strSRCH As String
strSRCH = txtSEARCH.Value
arrSRCH = Split(strSRCH, &quot;+&quot
Dim dbHIV As Database
Dim qdfKWSRCH As QueryDef
Set dbHIV = CurrentDb
strKWQUERY = &quot;select * from 1a where &quot;
For intARR = 0 To UBound(arrSRCH())
strKWQUERY = strKWQUERY & &quot;[1a.8] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.9] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.11] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.13] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.44] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.46] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot;
If (UBound(arrSRCH()) - intARR) > 0 Then
strKWQUERY = strKWQUERY & &quot; or &quot;
End If
Next intARR

msgbox strKWQUERY
docmd.openquery strKWQUERY
end sub


This is working fine, thanx very much for the help.

Shyam Shyam
cwizshyam@yahoo.com

 

[shyamsundar]

Dear CVigil & RJFost,

Thanx very much for your inputs. (Cvigil) excellent logic, I can use this in my future VB projects.

And, I discovered a Split Function in the MSDN CD, just after I posted this message, I am sorry I troubled you. Let me share u the split function
Private Sub cmdKYGO_Click()
If txtSEARCH.Value = &quot;Enter Search String&quot; Then
MsgBox &quot;Please type your Search String&quot;
txtSEARCH.SetFocus
Exit Sub
End If
Dim arrSRCH() As String
Dim strSRCH As String
strSRCH = txtSEARCH.Value
arrSRCH = Split(strSRCH, &quot;+&quot
Dim dbHIV As Database
Dim qdfKWSRCH As QueryDef
Set dbHIV = CurrentDb
strKWQUERY = &quot;select * from 1a where &quot;
For intARR = 0 To UBound(arrSRCH())
strKWQUERY = strKWQUERY & &quot;[1a.8] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.9] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.11] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.13] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.44] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot; _
& &quot; or [1a.46] like '*&quot; & arrSRCH(intARR) & &quot;*'&quot;
If (UBound(arrSRCH()) - intARR) > 0 Then
strKWQUERY = strKWQUERY & &quot; or &quot;
End If
Next intARR

msgbox strKWQUERY
docmd.openquery strKWQUERY
end sub


This is working fine, thanx very much for the help.

Shyam Shyam
cwizshyam@yahoo.com

 

[CVigil]

Excellent, Shyam! A built-in function that does the same will save you the overhead of building your own (or using the one I wrote)! Now that you mention it, the Split function seems familiar, so I've seen it before. I'm thinking that I've seen it in full-blown VB (I haven't done much coding in Access 2000 yet), because I just went to test Split in my Acces 97, and Split wasn't recognised. The compiler threw up an error and there is no help on the Split function. It's entirely possible that Split was added to Access 2000's VBA -- a good move in my opinion.

I recall that before I wrote MyStrArray (before this thread, for sure), I *looked* for a built-in function that would do the work for me, but didn't find it. Something had told me it was worth the look, but that was apparently a recollection from full-blown VB, as noted. There is an Array function, but that accepts a list of already separated terms, not a string containing a list of terms.

Well, great, Shyam! More good luck to you!
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot