Full Version: Using text box as a search input
UtterAccess Forums > Microsoft® Access > Access Forms
markmcrobie
The following code works as a crude way of entering text into a text box and searching the records on my continuous form based on the text box:
ode
-----------------------------------------------------
If Me.Text1 <> "(all)" Then
' Check whether there is any other criteria
' If so, add an "And"
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[TaskName] Like '* " & Me.Text1 & "*' "
End If
-----------------------------------------------------
It works - if my task name is "Remove gas tanks from main engine room" and I enter "gas" into the Text1 and press return, it'll find that record.
Obviously because of the * on either side of the text, it'll also find "gash", "gasoline", etc. This is fine.
What I'd like is to be able to search for 2 or more independent keywords within the same text box, maybe with a comma seperator or something.
Presently seraching for "gas,valve" will really be saying to Access "Like *gas,valve*" which will look for anything with the term "gas,valve" literally anywhere in the Task Name field. What I'd like it to do if "gas,valve" was entered in Text1 is search for "*gas* AND *valve*"
markmcrobie
Just noticed that this doesn't work if the field *starts* with what I'm searching for:
For example searching for "Gas" it will find the following:
Complete gas tank removal
but not:
gas tank removal
bceause the 2nd one starts with "gas". I'm guessing this is something to do with the leading * in my code.
markmcrobie
This now seems to solve the "field starting with" problem in post 2:
trWhere = strWhere & "[TaskName] Like '* " & Me.txtTaskName & "*' OR [TaskName] Like '" & Me.txtTaskName & "*'"
niesz
From your original post:
CODE
strWhere = strWhere & "[TaskName] Like '* " & Me.Text1 & "*' "

It's the space after the first asterisk that is causing the problems. You are actually searching for " gas" with this string. Remove the space and all will be well. You do not need the second piece of criteria.
niesz
To answer your original question, you'll have to parse out the criteria manually.
. Determine if there are any delimiters within the string.
2. Build the criteria dynamically with code using multiple ORs and LIKEs.
3. Execute the SQL
markmcrobie
Gulp!
Any idea where to start!
Thanks
niesz
My first stop would be the UA search. There should be many examples of this. Break each task down individually. You will first need to decide how to present it to your users. What will the delimiter be? Will there be a limit to the number of criteria? Would multiple textboxes be easier for them? Then just take it step by step. Use the InStr function to find the delimiters. Use the Left/Right/Mid functions to parse the criteria out. Use Do...Loops or For...Nexts in VBA to loop through. Concatenate the results into a string. Change the recordsource to this new string when you are done.
...Piece of cake... wink.gif
jmcwk
Try this:
lace a command button on your subform and in the click event of the button place the following:
DoCmd.FindRecord "Wh*", acAnywhere, False, , True, acCurrent, True
DoCmd.RunCommand acCmdFind
HTH
markmcrobie
What does the "Wh*" bit mean?
jmcwk
Mark,
You know I wish I could give you a specific however all I can tell you is that it is a wildcard that I have been using more than likely from back to the 97 Version???? Did it not work?
markmcrobie
It brings up the standard windows "Find" dialog, with "Wh*" literally already in the Search For box.
markmcrobie
Niesz:
on't laugh too much, but attached is an example of it what I managed so far on my own, with absolutely no help!
markmcrobie
Oops, try again!
MrSiezen
Hi Mark,
First, you should trim the strings for unneeded spaces
str1stWord = Trim(Left(Me.txtSearch, intDelimPos - 1))
str2ndWord = Trim(Right(Me.txtSearch, (intLength - intDelimPos)))
In the query builder, you forgot to repeat the [TaskName].
Me.txtSQL.Value = "SELECT * FROM qryWorkInstructions WHERE [TaskName] Like '*" & str1stWord & "*' OR [TaskName] Like '*" & str2ndWord & "*'"
With that solved, it'll work!
markmcrobie
Thanks!
asn't too bad for a 1st time ever writing my own code!
I was also thinking I don't need to store the length in a variable, I reckon it could maybe somehow be nested in the Right function?
Also I might need to build in some kinda way of checking if they haven't actually used a , (maybe they only want to search for 1 keyword?)
markmcrobie
I have a problem I don't think I can solve myself!
All the code above was just written in a blank db to see if I could do it.
Trouble is in my real db where I want to use it, the text box to accept search words is part of this code (txtTaskName, near the bottom)
I have no idea how to implement it in here!
Code
-----------------------------------------------------------------------------------
Public Function myFilter()
Dim strSQL As String, strWhere As String

If Me.cboStatus <> "(all)" Then
strWhere = strWhere & "[Status] = '" & Me.cboStatus & "'"
End If

If Me.cboVessel <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[VesselName] = '" & Me.cboVessel & "'"
End If

If Me.cboSection <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[SectionShortName] = '" & Me.cboSection & "'"
End If

If Me.cboProject <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[ProjectShortName] = '" & Me.cboProject & "'"
End If

If Me.cboHolder <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Holder] = '" & Me.cboHolder & "'"
End If

If Me.txtT3 <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[TaskCode] Like '" & Me.txtT3 & "*'"
End If

If Me.cbo1stLevel <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[FirstLevel] = '" & Me.cbo1stLevel & "'"
End If

If Me.txtTaskName <> "(all)" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[TaskName] Like '*" & Me.txtTaskName & "*'"
End If

strSQL = "SELECT * FROM qryWorkInstructions"
If Len(strWhere) > 0 Then
strSQL = strSQL & " Where " & strWhere
End If

strSQL = strSQL & ";"
Me.RecordSource = strSQL
With Me.RecordsetClone
If .BOF And .EOF Then
MsgBox "There are no records matching these criteria!"
Me.RecordSource = "SELECT * FROM qryWorkInstructions"
Me.cmdReset_Click
strSQL = "SELECT * FROM qryWorkInstructions;"
End If
End With
Me.txtSQL = strSQL
GetRecordCount
End Function
markmcrobie
I changed my code a bit, see what you think! (couple of minor changes, nothing to do with the problem in the post above).
might one day even get the hang of it!
markmcrobie
Changed again to capture if they only input 1 search word, therefore no comma delimiter
niesz
Very nice. Now, what if they have more than 2 things to search for? How about something like:

CODE
Option Compare Database
ption Explicit

Private Function myFilter()

    Dim intDelimPos As Integer
    Dim strLookFor As String
    Dim strWhereClause As String
    Dim strLogicOperator As String
    
    'Check for entry
    If Nz(Me.txtSearch) = "" Then Exit Function
    
    strLookFor = Me.txtSearch
    
    'Match Quality
    If Me.Match = 1 Then
        strLogicOperator = "AND"
    Else
        strLogicOperator = "OR"
    End If
    
    'Build WHERE clause
    Do
        intDelimPos = InStr(strLookFor, ",")
        
        If intDelimPos = 0 Then
            strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & _
            Trim(strLookFor) & "*'"
        Else
            strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & _
            Trim(Left(strLookFor, intDelimPos - 1)) & "*'"
        End If
        
        strLookFor = Mid(strLookFor, intDelimPos + 1)

    Loop Until intDelimPos = 0
    
    'Trim off leading Logic Operator
    strWhereClause = Trim(Mid(strWhereClause, 5))
    
    Me.txtSQL = "SELECT * FROM qryWorkInstructions WHERE " & strWhereClause
    Me.RecordSource = Me.txtSQL

End Function


For ease of copy/paste:

Option Compare Database
Option Explicit

Private Function myFilter()

Dim intDelimPos As Integer
Dim strLookFor As String
Dim strWhereClause As String
Dim strLogicOperator As String

'Check for entry
If Nz(Me.txtSearch) = "" Then Exit Function

strLookFor = Me.txtSearch

'Match Quality
If Me.Match = 1 Then
strLogicOperator = "AND"
Else
strLogicOperator = "OR"
End If

'Build WHERE clause
Do
intDelimPos = InStr(strLookFor, ",")

If intDelimPos = 0 Then
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & _
Trim(strLookFor) & "*'"
Else
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & _
Trim(Left(strLookFor, intDelimPos - 1)) & "*'"
End If

strLookFor = Mid(strLookFor, intDelimPos + 1)

Loop Until intDelimPos = 0

'Trim off leading Logic Operator
strWhereClause = Trim(Mid(strWhereClause, 5))

Me.txtSQL = "SELECT * FROM qryWorkInstructions WHERE " & strWhereClause
Me.RecordSource = Me.txtSQL

End Function

EDIT: You're probably wondering what Me.Match is. I changed your check box to an option group with two entries, 1-All Words, 2-Any words. Hope that didn't cofuse you.

hwn
niesz
There are easier ways to do this using the SPLIT function. It takes a string and splits it into an array using a supplied delimiter. But I don't think it was available with ACC97, so I wrote it the 'old fashioned' way.
markmcrobie
Brilliant, thanks - that was gonna be my next trick, trying to make it compatible with more than 2 words.
thought I could work out how to make it work with 3 or 4 or 5 (or any other specific number), but wasn't sure how to do it for an unlimited number (or unspecified).
Could you do me 1 more huge favour and talk me through what you changed to make it work, I'd really like to understand more.
Many thanks
niesz
I tried to comment it pretty good. Is there a specific part that you are not understanding?
markmcrobie
Well, having read it I kinda follow it, but:
Trim off leading Logic Operator
strWhereClause = Trim(Mid(strWhereClause, 5))
What does this do?
And in the line:
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & Trim(strLookFor) & "*'"
why is strWhereClause there right after = operator? Isn't there nothing in strWhereClause the 1st time the code runs?
niesz
'Trim off leading Logic Operator
strWhereClause = Trim(Mid(strWhereClause, 5))

If you notice as I build the criteria, I do not check to see whether there is any previous criteria there or not. I just start with " AND ..." or " OR ...". Then, when finished building ALL the criteria, I strip off the first 4 characters from the ENTIRE string. That way I don't have to keep checking whether previous criteria exists or not before adding the logic operator. With only two or three loops you probably won't see any performance hits, but if this were 100000 loops, it would be noticeably faster.

strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & Trim(strLookFor) & "*'"

It is true that it is empty the first time through. But the next time through it will not be. With this type of logic, you can continually add to the resulting string.
Edited by: niesz on Thu May 4 11:04:37 EDT 2006.
markmcrobie
Ok, but why 5 characters? surely as AND has 3 letters and OR only 2, the value to be stripped could be either 4 or 5?
Thanks
markmcrobie
And doesn't Trim only trim spaces?
niesz
5 because if you look closely I am adding "[space]AND[space]" or "[space]OR[space]"
want to start the criteria at the fifth letter regardless of which operator is used. Then the TRIM function strips off the leading space if 'OR' was used.
markmcrobie
Sorry, I'm still unsure of why it's the 5th character if OR is used. blush.gif
lso my next step is to allow the user to serach for exact match or partial match. Not sure how to go about it.
For example presently seraching "gas" will find "gas", "gassing", "gash", etc. I want to allow them to choose whether "gas" should find only "gas", or all the above.
Thanks for all your help, much appreciated.
niesz
After all the criteria is parsed you'll end up with either:
AND [TaskName] Like ...."
..or..
" OR [TaskName] Like ..."
When that line of code runs, the fifth character of the "and" version is the space before the open square bracket ([). So you'll end up with:
" [TaskName] Like ..."
If the "or" version was used, the fifth character IS the open square bracket ([). So you'll end up with:
"[TaskName] Like ..."
Then, in both cases you TRIM the string your left with, which in the first scenario removes the leading space, and in the second scenario, does nothing.
HAs far as your exact match goes, place a checkbox or option group on your form. In the beginning of your code, check for the switch like:
Dim strExactSwitch as string
If Me.ExactMatch Then
strExactMatch = "*"
Else
strExactMatch = ""
End IF
Then any line with asterisks...
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & _
Trim(strLookFor) & "*'"
to something like...
CODE
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '" & strExactMatch & _
Trim(strLookFor) & strExactMatch & "'"
markmcrobie
Not sure how that new code could work - we're either appending a * or nothing to either side of strLookFor .
However if we append nothing, we'll be looking for, say, "steam". This will only find records where Task Name is, exactly, "steam", not "Steam clean" or whatever.
What I want is if the Exact Match check box is ticked, searching for "steam" will find "Steam clean" but not "Steaming clean". If it's not checked, it'll find both in that example.
Thanks
niesz
Then it is not an exact match. In order for what you are wanting to work you would have to search for:
* steam *" or "* steam" or "steam *"
The code should still be able to be adapted.
markmcrobie
Ok, so I think I want a check box called "Whole words only".
If ticked, I'll search for "* steam *" or "* steam" or "steam *".
If unticked I'll search for "*steam*"
Am I on the right track?
niesz
Looks good to me. It just depends on how you want it to work.
markmcrobie
I think that's the way. I've had a look at how to incorporate it into the code, but I'm getting a bit bewildered.
niesz
Post what you have so far and we'll see what we can do.
markmcrobie
Ok, I'm going along the lines of:
im strWholeWordStart
Dim strWholeWordEnd
If Me.chkWholeWord = 1 Then
strWholeWordStart = "* "
strWholeWordEnd = " *"
Else
strWholeWordStart = "*"
strWholeWordEnd = "*"
End If
At the next bit:
strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '" & strWholeWordStart & Trim(strLookFor) & strWholeWordEnd & "'"
Ocan't check it right now as my Access installation went wonky and the IT people are re-installing it today, but I hope by looking at the code you can tell me if my thinking is ok.
Thanks a lot for all your help, truly appreciated.
niesz
I think you want something like:

CODE
If Me.chkWholeWord Then
nbsp; strWhereClause = strWhereClause & " " & strLogicOperator & " ([TaskName] Like '* " & Trim(strLookFor) & " *'"
  strWhereClause = strWhereClause & " OR [TaskName] Like '* " & Trim(strLookFor) & "'"
  strWhereClause = strWhereClause & " OR [TaskName] Like '" & Trim(strLookFor) & " *')"
Else
  strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & Trim(strLookFor) & "*'"
End If


O think this is what you want, but I typed it on the fly and I haven't had much coffee yet. frown.gif
markmcrobie
Is my code wrong?
niesz
What code are you referring to? Post the latest version.
markmcrobie
The code I was thinkinh about a few posts above.
niesz
If you're referring to:

Dim strWholeWordStart
Dim strWholeWordEnd

If Me.chkWholeWord = 1 Then
strWholeWordStart = "* "
strWholeWordEnd = " *"
Else
strWholeWordStart = "*"
strWholeWordEnd = "*"
End If

At the next bit:

strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '" & strWholeWordStart & Trim(strLookFor) & strWholeWordEnd & "'"

...then, yes, it is incorrect. The resulting string you would end up with when looking for an exact word match would be "* yourword *". This would only find the text when it is embedded inside of other text because of the leading and trailing spaces. That's why I suggested what I did. It would have to search for:

"* yourword *" ............for words within the string
"yourword *" ............for words at the front of the string
"* yourword" ............for words at the end if the string

EDIT:
..and now that I think about it, it would also need to search for:

"yourword"

..by itself, in case the word stood alone as the entire text. So it would have to look like:

CODE
If Me.chkWholeWord Then
nbsp; strWhereClause = strWhereClause & " " & strLogicOperator & " ([TaskName] Like '* " & Trim(strLookFor) & " *'"
  strWhereClause = strWhereClause & " OR [TaskName] Like '* " & Trim(strLookFor) & "'"
  strWhereClause = strWhereClause & " OR [TaskName] Like '" & Trim(strLookFor) & " *'"
  strWhereClause = strWhereClause & " OR [TaskName] = '" & Trim(strLookFor) & "')"
Else
  strWhereClause = strWhereClause & " " & strLogicOperator & " [TaskName] Like '*" & Trim(strLookFor) & "*'"
End If
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.