Search Box Problem

[nik707]

Hello all,

First of all my name is Shan and I am currently learning and also designing a database in Access. Your forum users seems to be very helpful and experts in this matter so I thought I could ask around a question or two.

I have a table with around 15 fields, 2 date fields and rest text.

I designed a search form which uses the "LIKE" function to search.

Howevever, some of the fields have multiple keywords, sperated by commas

Eg: Field Name: Author

1. James B, Johnathan D
2. Matt W, Steve J

Those are 2 entries...now if I search for "James B, Johnathan D" then it gives the result [I am not using a query to do this, I am actually defining the text entered as the criteria and then using that to search my table.field then returning that to the input form where I input data]

However, if I type "Jonathan D, James B" in search texbox then it wont return any results...as we know the LIKE function compares word by word and my criteria is in reverse

Is there any way to modify (or new way) to make it so that the each search string within commas would be compared. What I am saying is say you enter "Johnathan D, James B" as my search string

Then it should take "Johnathan D" and search the Author field (say it finds 1 occasion) then it would take "James B" and search the Author field and (say it finds same occasion which is record 1 mentioned above ) and since both returned 1 occasion, it would display that record? or is there other ways/easier ways to do this?

Thank you and please let me know if I did not explain this well.

Thanks again for your time!

Cheers,
Shan

[Megalog]

Try this function:

Public Function GetResults(strPassed As String, blnExactMatch As Boolean) As String
'Builds Like statements from comma delimited strings
Dim tmpString As String
Dim tmpLeft As String

'Determines whether you're searching for an exact match, or a 'contains' match
If blnExactMatch Then
    
    GetResults = "Like '" & strPassed & "'"

Else
    'Checks to see if commas exist, if so then it loops through each value, else it builds the like statment below
    If InStr(strPassed, ",") > 0 Then
        tmpString = strPassed
        'For every comma found in strPassed, loop through and add the value before the comma to the Like statement
        Do While InStr(tmpString, ",") > 0
            tmpLeft = Left(tmpString, InStr(tmpString, ",") - 1)
                If GetResults = "" Then
                    GetResults = "Like '*" & tmpLeft & "*'"
                Else
                    GetResults = GetResults & " AND Like '*" & tmpLeft & "*'"
                End If
            'tmpString gets the left-most value removed, and is trimmed of spaces before looping again
            tmpString = Trim(Right(tmpString, Len(tmpString) - Len(tmpLeft) - 1))
        Loop
        
        'This takes care of the last value in the string
        If Len(tmpString) > 0 Then
            GetResults = GetResults & " AND Like '*" & tmpString & "*'"
        End If
    
    Else
        GetResults = "Like '*" & strPassed & "*' "
    End If
    
End If
End Function

On your form, you'll need a text box (for the search string), and a toggle button or checkbox for the boolean switch used to search for either an exact match, or using wildcards for a containing match. You can also just hardcode it as True or False instead of having another control.

Then you would do the following on whichever event you want to call the search:

GetResults([Your Text Box Field],[True/False, or a boolean control name])

Remember, this just builds the 'Like' statement, so work the function into where you need it (in a query design, form code, etc).

Good luck =)
-Mega (waiting for Neo or Adezii to come along and offer a 1-line solution =P )

[NeoPa]

Rather than a single string to compare, if you break down your requirement logically, you will see that you really want to compare two strings separately, albeit within the same field.

If you enter them in two form TextBox controls (txtSearch1 & txtSearch2) then you can make your WHERE clause (or filter) of the form :

strSQL = "... " & _
         "WHERE [Author] Like '" & Me.txtSearch1 & "*' " & _
         "  AND [Author] Like '" & Me.txtSearch2 & "*'"

PS. Welcome to Bytes Shan :)

[nik707]

Rather than a single string to compare, if you break down your requirement logically, you will see that you really want to compare two strings separately, albeit within the same field.

If you enter them in two form TextBox controls (txtSearch1 & txtSearch2) then you can make your WHERE clause (or filter) of the form :

strSQL = "... " & _
         "WHERE [Author] Like '" & Me.txtSearch1 & "*' " & _
         "  AND [Author] Like '" & Me.txtSearch2 & "*'"

PS. Welcome to Bytes Shan :)

Thanks for the warm welcome and spot on answers both of you!!

The function looks good rather than putting 2 text boxes because the problem I have overall.

Its because the author field can contain anything up to 5 authors (although I dont expect the user to search more than 3).

Not just that, I also have other fields...about 5 to say such as Publisher, People mentioned, Companies Mentioned, Legislations Mentioned and so on...

so that would be alot of text boxes...which is why calling function for each field might be easier choice (in my view) I will put this code in (I am planning to put in pair of option boxes for the user to select if the search is for a single word or multiple keyword) then try it out and let you guys know!!

Just one last question though...why do we still need the boolean value passed to the function...can' t we just check to see if the txtSearchString (entered in the text box) have a comma, if so then it should be treated as multiple keyword search? (or in that case...if comma is found, then assign the boolean value as false)

Cheers m8s!
Shan

[Megalog]

The boolean portion doesnt determine if you're using a single or multiple value search, it's used to decide if you want an exact match or not (basically it's just adding the wildcard asterisk to the like statement). I added the option so that if you only specify one value, you have the option of returning records that have only that exact value, or records that have the value inside. It's purely up to you.
If you want to get rid of that abililty, then eliminate the variable in line 1, and delete lines 7-11, and line 36.

[Megalog]

Actually, looking back on this, this function wont work as it's intended to.. Every separate like statement needs to have a field referenced. I'll put up a revision later on, sorry!

[Megalog]

Public Function GetResults(strPassed As String, strFieldName As String, blnExactMatch As Boolean) As String
'Builds Like statements from comma delimited strings
Dim tmpString As String
Dim tmpLeft As String
 
'Determines whether you're searching for an exact match, or a 'contains' match
If blnExactMatch Then
    
    GetResults = "(((" & strFieldName & ") Like '" & strPassed & "'))"
 
Else
    'Checks to see if commas exist, if so then it loops through each value, else it builds the like statment below
    If InStr(strPassed, ",") > 0 Then
        tmpString = strPassed
        'For every comma found in strPassed, loop through and add the value before the comma to the Like statement
        Do While InStr(tmpString, ",") > 0
            tmpLeft = Left(tmpString, InStr(tmpString, ",") - 1)
                If GetResults = "" Then
                    GetResults = "(" & strFieldName & ") Like '*" & tmpLeft & "*'"
                Else
                    GetResults = GetResults & " AND (" & strFieldName & ") Like '*" & tmpLeft & "*'"
                End If
            'tmpString gets the left-most value removed, and is trimmed of spaces before looping again
            tmpString = Trim(Right(tmpString, Len(tmpString) - Len(tmpLeft) - 1))
        Loop
        
        'This takes care of the last value in the string
        If Len(tmpString) > 0 Then
            GetResults = "((" & GetResults & " AND (" & strFieldName & ") Like '*" & tmpString & "*'" & "))"
        End If
    
    Else
        GetResults = "(((" & strFieldName & ") Like '*" & strPassed & "*'))"
    End If
    
End If
End Function

Ok, now it's even messier =) I added in the field name, and modified the code to properly reconstruct the string. If you have any spaces in the field name you're searching, be sure to use brackets around it. Also, if you're using multi-value fields (in access 2007), you have to use [Fieldname].[Value]

I'm not sure how you are exactly filtering the form.. are you modifying the form recordsource, or just re-applying the filter?

Either way, test it out and see if it fits your needs.

me.Filter = GetResults(me.txtBoxName,"[Authors]", me.chkBoxName)
me.FilterOn = true

[NeoPa]

If you're going to use multiple name entries in a single control, then I suggest you use a different separator character, as commas (,) are often used within names anyway, to indicate the surname is entered before the first name.

A simple way to separate the individual entries then (assuming you use a semi-colon (;) say) would be to use the Split() function :

varYourVariantVariable = Split(Me.txtSearch, ";")
Debug.Print varYourVariantVariable(0)    'First element (name)
Debug.Print varYourVariantVariable(1)    'Second element
...                                      '...
Debug.Print varYourVariantVariable(UBound(varYourVariantVariable))
                                         'Last element

[nik707]

The thing is, I've already got a database so I cant really use any other seperators (that means I have to go on and modify around 500 records)

Now, I see you are filtering the results according the result of that function.

This is the code I have for now (ending part of it)

My table name: Entry
The combo search field is assigned to criteria1 (don't worry about this)

 'Generate search criteria
       GCriteria2 = "Author LIKE '*" & txtSearchString2 & "*'"
       Gcriteria3 = "Publisher LIKE '*" & txtSearchString3 & "*'"
                
       sSQL = "SELECT * FROM ENTRY "
       sSQL = sSQL & "WHERE ((" & GCriteria1 & ")"
        
       sCaption = "(" & cboSearchField1.Value & " contains '*" & txtSearchString1 & "'"
        
       If IsNull(cboSearchField2) Then
           sSQL = sSQL & ")"
           sCaption = sCaption & ")"

       Else
           sSQL = sSQL & " AND (" & GCriteria2 & ")"
	   sCaption = sCaption & " Author contains '*" & txtSearchString2 & "'"
       End If

       If IsNull(cboSearchField3) Then
           If Not IsNull(cboSearchField2) Then sSQL = sSQL & ")"

       Else
           sSQL = sSQL & " AND (" & Gcriteria3 & "))"
           sCaption = sCaption & " Publisher contains '*" & txtSearchString3 & "'"
       End If

       Form_frmInput.RecordSource = sSQL
      
       Form_frmInput.Caption = "Entry " & sCaption
        
       'Close frmSearch
       DoCmd.Close acForm, "frmSearch"
        
       MsgBox "Results have been filtered."
        
   End If

So as you can see, I am returning the current search results to the input form (which is called frmInput through where I use to input data)

My problem now is (since the search code you guys wrote is going to be done in separate function) is to call that function and return the result to criteria2 and criteria3 (accordingly for author and publisher...the n it would go on to display them in my input form)

From my C coding experience, would it be something like this?

'for GCriteria1; optSearch is the option group frame name
 
GCriteria2 = GetResults(me.txtSearchField2,"[Authors]", me.optSearch)
GCriteria3 = GetResults(me.txtSearchField3,"[Publishers]", me.optSearch)

1. If so where should this line go? (I believe it should replace line 1 & 2?)


2. Should any other lines be removed? (my concerns are line 16 & 24)


3. Whats "me.FilterO n = true" do and is it still essential?

Thanks again for your time!

[Megalog]

Hmm...
I was under the assumption that the search was only going to be ran against one field at a time, since it basically supplies the entire criteria section after the WHERE statement. If you modify how the function formats the output (basically playing with how it places parenthesis), you should be able to AND together the output criterias into a final string which is placed behind the WHERE statement.

Roughly like:

If not isnull(cboSearchField1) then
      GCriteria1 = GetResults(me.txtSearchField1,"[Country]", me.optSearch)
      GCriteria = GCriteria1
End if

If not isnull(cboSearchField2) then
      GCriteria2 = GetResults(me.txtSearchField2,"[Author]", me.optSearch)
      If GCriteria = "" then
          GCriteria = GCriteria2
     Else
          GCriteria = GCriteria & " AND " & GCriteria2
     End if
End if

If not isnull(cboSearchField3) then
      GCriteria3 = GetResults(me.txtSearchField3,"[Publishers]", me.optSearch)
      If GCriteria = "" then
          GCriteria = GCriteria3
     Else
          GCriteria = GCriteria & " AND " & GCriteria3
     End if
End if

sSQL = sSQL & "WHERE ((" & GCriteria & ")"

The first part (lines 1-22) checks to see if search criteria exist on the form, if so it sends it to the proper function. Then it builds the 'GCriteria' depending on if values are present or not. It can probably be optimized with less code to run in a loop or select case later on.

Unfortunately, I'm not going to have any time to revisit this until maybe Monday.

Oh, and to answer your other question, the me.filter/me.filteron isnt needed in your case, so you can disregard that.

Good luck!

[NeoPa]

The thing is, I've already got a database so I cant really use any other seperators (that means I have to go on and modify around 500 records)
...

Shan, I suggest you reread my last post as you seem to have taken the reverse meaning from it than it actually says.

The separator character would be one that specifically DOESN'T match any of the data in your table.

Do be careful of ignoring something simply because you don't understand very well what it's about. The suggestion is there generally because it's a way to avoid predictable situations. If you ignore it, then you will typically end up bumping into the problems you could have avoided. Imagine how you would approach someone asking you a question that only occurs BECAUSE they ignored your last piece of advice.

It may well be that they have misunderstood your question, but neither will understand this unless you ask for clarification.

[nik707]

Shan, I suggest you reread my last post as you seem to have taken the reverse meaning from it than it actually says.

The separator character would be one that specifically DOESN'T match any of the data in your table.

Do be careful of ignoring something simply because you don't understand very well what it's about. The suggestion is there generally because it's a way to avoid predictable situations. If you ignore it, then you will typically end up bumping into the problems you could have avoided. Imagine how you would approach someone asking you a question that only occurs BECAUSE they ignored your last piece of advice.

It may well be that they have misunderstood your question, but neither will understand this unless you ask for clarification.

I appreciate it Neo. I do understand it and as you explained, yes I am in trouble :) but at this point I think I am passed a point where I could do anything as I am not actually doing the data entry (I am designing it for a friend and as a blue print for something I am planning to do in future) I am also writing a small manual to explain what to put in and what not to put in.

As of now I am allowing commas in places such as Author (names in the format "James, B") and other fields where multiple entries are allowed.

In few field, I am not allowing commas or special characters such as title etc.

At the end my plan is to use the function above for fields where multiple entries can be used and not use it for fields such as title and author (where the user will put in commas)

And by the way, I really do appreciate your concern, I guess I did not put my intentions well in that post which might looked like I ignored your advice. Sorry and thanks again!! You guys are great!

One more question though. What drives you guys to come up here and answer questions (I mean I am a newbie and I just registered here to ask questions, I am not even a contributor to this community) I know database programmers can earn 80k upwards...so why here? writing free coding and giving advice?

Thanks!

[nik707]

I did not get this part though

Why are we seeing if the combo box is not null?

If not isnull(cboSearchField2) then

1. Isn't that supposed to be like this?

If not isnull(txtSearchField2) then

2. Now one of them is a combo box and it doesn't need to check if commas exists...so would that be like this?

  GCriteria3 = cboSearchField1.Value

But then again ^ that doesn't really look for the value, its rather assigning it to the criteria, how can I be able to look for that value and return results that contain that value (well, along with other values that are entered in the two text boxes using AND method)

An error that poped up while compiling the current code (after I made several changes mentioned above



Any help is appreciated!

[NeoPa]

...
One more question though. What drives you guys to come up here and answer questions (I mean I am a newbie and I just registered here to ask questions, I am not even a contributor to this community) I know database programmers can earn 80k upwards...so why here? writing free coding and giving advice?

Thanks!

Most of us here actually enjoy this side of our work. We have some free time and we know the world will be a better place with people giving as well as taking. Of course we all start by needing some help, and when you get something for free, you're often inspired to return the favour. If you're not careful, you find you actually quite enjoy helping people.

Besides, you also get to "meet" people (virtually in most cases) and the community here at Bytes is quite active and good-natured.

Did I mention that it also helps to keep your skills toned. I've learned lots of things here that I never really needed to know at work directly, but knowing them makes me a better programmer.

[NeoPa]

whoops

another error popped up :(
...

Shan,

I suspect we will need the values in Me.txtSearchFie ld1 and Me.optSearch.

PS. Please post your code in the tags provided (in future). Your code is posted very clearly, but for various reasons (principally copy/pasting) we do require they be posted in the tags.