I believe RJFost missed the point of shyamsundar's request. Shyam, you want to allow a user to enter multiple search criteria, for instance "sugar" and "milk". The syntax you would have them use for such a search is "sugar, milk", although you would be willing to use the syntax as "sugar + milk" 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 "sugar" and "milk", would you A) want your search to return all records containing "sugar" and all records containing "milk", 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 "sugar" AND "milk"] or else [records containing "sugar" OR "milk"]).
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:
Code:
'************************
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:
Code:
'************************
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 "processed string". In your hypothetical case, if the user enetered "sugar,milk", then in your code, varVariable = MyStrArray(strSEARCH.text,","
should result in varVariable containing an array; and varVariable(0) should contain "sugar", and varVariable(1) should contain "milk". 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:
Code:
'************************
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,
Code:
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
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers"
![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
