query

[OD]

I have a form that allows the user to enter search ctiteria by placing a comma
between the criteria. I'm using an Access table. The problem is that I don't
know
how many search criteria I will have. right now I have 3 forms that I use
for this.
I would like to use just one form.

Private Sub EnterButton_Click()

'..[On the click of the enter key, gets the string entered into the textbox.
'Then parses the string to see how many search criteria we have. After
'getting the number I will know what form to open. I have Public funtions
setup
'to hold the varSearchStr

'EXAMPLE:
'..[ Function used in query criteria.

'Public Function SearchStr1() As String
'SearchStr1 = varSearchStr1
'End Function
'..]



']..

Dim x, varCounter As Integer
Dim varMyStr As String

'..[Gets the lenth of the info entered into the textbox.

x = Len(Me.TypedText)
']..

'..[Makes sure the vars are empty.

varSearchStr1 = ""
varSearchStr2 = ""
varSearchStr3 = ""
']..

varCounter = 1 '..[Sets varcounter to 1.]..



'..Loop used to parse the string entered into the textbox, and
'sets varCounter with the number of search criteria. User enters
'a comma between the search criteria.

For i = 1 To x
varMyStr = Mid(Me.TypedText, i, 1)
If varMyStr = "," Then
varMyStr = ""
varCounter = varCounter + 1
Else
Select Case varCounter
Case 1
varSearchStr1 = varSearchStr1 + varMyStr
Case 2
varSearchStr2 = varSearchStr2 + varMyStr
Case 3
varSearchStr3 = varSearchStr3 + varMyStr
End Select
End If
Next i
'..]

'..[Case statement to see what form to open.

Select Case varCounter
Case 1
DoCmd.OpenForm "Results1"
Case 2
DoCmd.OpenForm "Results2"
Case 3
DoCmd.OpenForm "Results3"
End Select
'..]

'..[EXAMPLE used in form query.

'SELECT Items.Isle, Items.Item, Items.[Item Search]
'FROM Items
'WHERE (((Items.[Item Search]) Like "*"+SearchStr1()+"*"
' Or (Items.[Item Search]) Like "*"+SearchStr2()+"*"));

'..]

End Sub


Plus I'm also tring to do the same thing using VB and SQL, I can not seem to
be
able to pass a varible to the SQL query.


Thanks
OD

 

[pietlinden]

I have a form that allows the user to enter search ctiteria by placing a comma
between the criteria. I'm using an Access table. The problem is that I don't
know
how many search  criteria I will have. right now I have 3 forms that I use
for this.
I would like to use just one form.

Private Sub EnterButton_Click()

'..[On the click of the enter key, gets the string entered into the textbox.
'Then parses the string to see how many search criteria we have. After
'getting the number I will know what form to open. I have Public funtions
setup
'to hold the varSearchStr

    'EXAMPLE:
    '..[ Function used in query criteria.

    'Public Function SearchStr1() As String
        'SearchStr1 = varSearchStr1
    'End Function
    '..]

Since the search criteria are always delimited, you can use Split to
break the string into separate chunks (minus the delimiter). Then you
can loop through that array and OR your criteria together.

here's something that may get you started... or may just confuse the
hell out of you...
Public Function BuildWhereClauseFromDelimitedString(ByVal strField As
String, ByVal strDelimitedString As String, strDelimiter As String) As
String
Dim varResult As Variant '--- an array to put the split values
into.
Dim intCounter As Integer '--- increment variable to loop over
array
Dim strWhere As String '--- temp variable to store where
clause while building
Dim strCriteriaType As String
Dim strDelim As String

varResult = Split(strDelimitedString, strDelimiter)

Select Case True
Case IsNumeric(varResult(0))
'no delimiter
Case IsDate(varResult(0))
strDelim = "#"
Case Else
strDelim = "'"
End Select

For intCounter = LBound(varResult) To UBound(varResult)
strWhere = strWhere & "[strField]=" & strDelim & varResult
(intCounter) & strDelim & " OR " & vbCrLf
Next intCounter

'remove the last/extra " OR "
strWhere = Trim(Left$(strWhere, Len(strWhere) - 5))

BuildWhereClauseFromDelimitedString = "WHERE " & strWhere
End Function

sample Call:
?buildwhereclausefromdelimitedstring
("MyField","Dog,Cat,Elephant,Horse",",")


Sample Ouput:
WHERE [strField]='Dog' OR
[strField]='Cat' OR
[strField]='Elephant' OR
[strField]='Horse'

You would have to basically modify the Query's SQL statement on the
fly, though... so either assign the new SQL statement to an existing
querydef
(e.g. qdf.SQL="SELECT ... FROM... " &
BuildWhereClauseFromDelimitedString
("MyField","Dog,Cat,Elephant,Horse",",") & ";"

How's that for confusing the issue?