By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Search Box Problem

P: 20
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
Jun 4 '08 #1
Share this Question
Share on Google+
41 Replies


Megalog
Expert 100+
P: 378
Try this function:

Expand|Select|Wrap|Line Numbers
  1. Public Function GetResults(strPassed As String, blnExactMatch As Boolean) As String
  2. 'Builds Like statements from comma delimited strings
  3. Dim tmpString As String
  4. Dim tmpLeft As String
  5.  
  6. 'Determines whether you're searching for an exact match, or a 'contains' match
  7. If blnExactMatch Then
  8.  
  9.     GetResults = "Like '" & strPassed & "'"
  10.  
  11. Else
  12.     'Checks to see if commas exist, if so then it loops through each value, else it builds the like statment below
  13.     If InStr(strPassed, ",") > 0 Then
  14.         tmpString = strPassed
  15.         'For every comma found in strPassed, loop through and add the value before the comma to the Like statement
  16.         Do While InStr(tmpString, ",") > 0
  17.             tmpLeft = Left(tmpString, InStr(tmpString, ",") - 1)
  18.                 If GetResults = "" Then
  19.                     GetResults = "Like '*" & tmpLeft & "*'"
  20.                 Else
  21.                     GetResults = GetResults & " AND Like '*" & tmpLeft & "*'"
  22.                 End If
  23.             'tmpString gets the left-most value removed, and is trimmed of spaces before looping again
  24.             tmpString = Trim(Right(tmpString, Len(tmpString) - Len(tmpLeft) - 1))
  25.         Loop
  26.  
  27.         'This takes care of the last value in the string
  28.         If Len(tmpString) > 0 Then
  29.             GetResults = GetResults & " AND Like '*" & tmpString & "*'"
  30.         End If
  31.  
  32.     Else
  33.         GetResults = "Like '*" & strPassed & "*' "
  34.     End If
  35.  
  36. End If
  37. 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:
Expand|Select|Wrap|Line Numbers
  1. 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 )
Jun 4 '08 #2

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "... " & _
  2.          "WHERE [Author] Like '" & Me.txtSearch1 & "*' " & _
  3.          "  AND [Author] Like '" & Me.txtSearch2 & "*'"
PS. Welcome to Bytes Shan :)
Jun 5 '08 #3

P: 20
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "... " & _
  2.          "WHERE [Author] Like '" & Me.txtSearch1 & "*' " & _
  3.          "  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
Jun 5 '08 #4

Megalog
Expert 100+
P: 378
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.
Jun 5 '08 #5

Megalog
Expert 100+
P: 378
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!
Jun 5 '08 #6

Megalog
Expert 100+
P: 378
Expand|Select|Wrap|Line Numbers
  1. Public Function GetResults(strPassed As String, strFieldName As String, blnExactMatch As Boolean) As String
  2. 'Builds Like statements from comma delimited strings
  3. Dim tmpString As String
  4. Dim tmpLeft As String
  5.  
  6. 'Determines whether you're searching for an exact match, or a 'contains' match
  7. If blnExactMatch Then
  8.  
  9.     GetResults = "(((" & strFieldName & ") Like '" & strPassed & "'))"
  10.  
  11. Else
  12.     'Checks to see if commas exist, if so then it loops through each value, else it builds the like statment below
  13.     If InStr(strPassed, ",") > 0 Then
  14.         tmpString = strPassed
  15.         'For every comma found in strPassed, loop through and add the value before the comma to the Like statement
  16.         Do While InStr(tmpString, ",") > 0
  17.             tmpLeft = Left(tmpString, InStr(tmpString, ",") - 1)
  18.                 If GetResults = "" Then
  19.                     GetResults = "(" & strFieldName & ") Like '*" & tmpLeft & "*'"
  20.                 Else
  21.                     GetResults = GetResults & " AND (" & strFieldName & ") Like '*" & tmpLeft & "*'"
  22.                 End If
  23.             'tmpString gets the left-most value removed, and is trimmed of spaces before looping again
  24.             tmpString = Trim(Right(tmpString, Len(tmpString) - Len(tmpLeft) - 1))
  25.         Loop
  26.  
  27.         'This takes care of the last value in the string
  28.         If Len(tmpString) > 0 Then
  29.             GetResults = "((" & GetResults & " AND (" & strFieldName & ") Like '*" & tmpString & "*'" & "))"
  30.         End If
  31.  
  32.     Else
  33.         GetResults = "(((" & strFieldName & ") Like '*" & strPassed & "*'))"
  34.     End If
  35.  
  36. End If
  37. 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.

Expand|Select|Wrap|Line Numbers
  1. me.Filter = GetResults(me.txtBoxName,"[Authors]", me.chkBoxName)
  2. me.FilterOn = true
Jun 5 '08 #7

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. varYourVariantVariable = Split(Me.txtSearch, ";")
  2. Debug.Print varYourVariantVariable(0)    'First element (name)
  3. Debug.Print varYourVariantVariable(1)    'Second element
  4. ...                                      '...
  5. Debug.Print varYourVariantVariable(UBound(varYourVariantVariable))
  6.                                          'Last element
Jun 6 '08 #8

P: 20





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)

Expand|Select|Wrap|Line Numbers
  1.  'Generate search criteria
  2.        GCriteria2 = "Author LIKE '*" & txtSearchString2 & "*'"
  3.        Gcriteria3 = "Publisher LIKE '*" & txtSearchString3 & "*'"
  4.  
  5.        sSQL = "SELECT * FROM ENTRY "
  6.        sSQL = sSQL & "WHERE ((" & GCriteria1 & ")"
  7.  
  8.        sCaption = "(" & cboSearchField1.Value & " contains '*" & txtSearchString1 & "'"
  9.  
  10.        If IsNull(cboSearchField2) Then
  11.            sSQL = sSQL & ")"
  12.            sCaption = sCaption & ")"
  13.  
  14.        Else
  15.            sSQL = sSQL & " AND (" & GCriteria2 & ")"
  16.        sCaption = sCaption & " Author contains '*" & txtSearchString2 & "'"
  17.        End If
  18.  
  19.        If IsNull(cboSearchField3) Then
  20.            If Not IsNull(cboSearchField2) Then sSQL = sSQL & ")"
  21.  
  22.        Else
  23.            sSQL = sSQL & " AND (" & Gcriteria3 & "))"
  24.            sCaption = sCaption & " Publisher contains '*" & txtSearchString3 & "'"
  25.        End If
  26.  
  27.        Form_frmInput.RecordSource = sSQL
  28.  
  29.        Form_frmInput.Caption = "Entry " & sCaption
  30.  
  31.        'Close frmSearch
  32.        DoCmd.Close acForm, "frmSearch"
  33.  
  34.        MsgBox "Results have been filtered."
  35.  
  36.    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...then it would go on to display them in my input form)

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

Expand|Select|Wrap|Line Numbers
  1. 'for GCriteria1; optSearch is the option group frame name
  2.  
  3. GCriteria2 = GetResults(me.txtSearchField2,"[Authors]", me.optSearch)
  4. GCriteria3 = GetResults(me.txtSearchField3,"[Publishers]", me.optSearch)
  5.  

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.FilterOn = true" do and is it still essential?

Thanks again for your time!
Jun 6 '08 #9

Megalog
Expert 100+
P: 378
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. If not isnull(cboSearchField1) then
  4.       GCriteria1 = GetResults(me.txtSearchField1,"[Country]", me.optSearch)
  5.       GCriteria = GCriteria1
  6. End if
  7.  
  8. If not isnull(cboSearchField2) then
  9.       GCriteria2 = GetResults(me.txtSearchField2,"[Author]", me.optSearch)
  10.       If GCriteria = "" then
  11.           GCriteria = GCriteria2
  12.      Else
  13.           GCriteria = GCriteria & " AND " & GCriteria2
  14.      End if
  15. End if
  16.  
  17. If not isnull(cboSearchField3) then
  18.       GCriteria3 = GetResults(me.txtSearchField3,"[Publishers]", me.optSearch)
  19.       If GCriteria = "" then
  20.           GCriteria = GCriteria3
  21.      Else
  22.           GCriteria = GCriteria & " AND " & GCriteria3
  23.      End if
  24. End if
  25.  
  26. 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!
Jun 6 '08 #10

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jun 9 '08 #11

P: 20
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!
Jun 9 '08 #12

P: 20
I did not get this part though

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

Expand|Select|Wrap|Line Numbers
  1. If not isnull(cboSearchField2) then
1. Isn't that supposed to be like this?

Expand|Select|Wrap|Line Numbers
  1. 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?

Expand|Select|Wrap|Line Numbers
  1.   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!
Jun 9 '08 #13

NeoPa
Expert Mod 15k+
P: 31,489
...
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.
Jun 9 '08 #14

NeoPa
Expert Mod 15k+
P: 31,489
whoops

another error popped up :(
...
Shan,

I suspect we will need the values in Me.txtSearchField1 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.
Jun 9 '08 #15

P: 20
Here is the full repost

I did not get this part though

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

Expand|Select|Wrap|Line Numbers
  1.       If not isnull(cboSearchField2) then
1. Isn't that supposed to be like this?


Expand|Select|Wrap|Line Numbers
  1.       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?

Expand|Select|Wrap|Line Numbers
  1.  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)

This is the screen shot of combo box and its properties




3. An error that poped up while compiling the current code (after I made several changes mentioned above (the screenshot of error above and the code involved is below)

** remember, I am returning the whole search result (if all 3 of the user input data are available then it should show that result in frmInput, where I input data)

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSea_Click()
  2.  
  3.     If IsNull(optSearch) Then
  4.     MsgBox "You must select one of the option boxes to define your search!"
  5.  
  6.     Else
  7.  
  8.     If Not IsNull(txtSearchField1) Then
  9.         GCriteria1 = GetResults(Me.txtSearchField1, "[Country]", Me.optSearch)
  10.         GCriteria = GCriteria1
  11.  
  12.     End If
  13.  
  14.     If Not IsNull(txtSearchField2) Then
  15.         GCriteria2 = GetResults(Me.txtSearchField2, "[Author]", Me.optSearch)
  16.  
  17.         If GCriteria = "" Then
  18.            GCriteria = GCriteria2
  19.  
  20.         Else
  21.               GCriteria = GCriteria & " AND " & GCriteria2
  22.         End If
  23.      End If
  24.  
  25.     If Not IsNull(cboSearchField1) Then
  26.         GCriteria3 = GetResults(cboSearchField1.Value, "[EconomicSector]", Me.optSearch)
  27.  
  28.             If GCriteria = "" Then
  29.                GCriteria = GCriteria3
  30.  
  31.            Else
  32.                    GCriteria = GCriteria & " AND " & GCriteria3
  33.            End If
  34.     End If
  35.  
  36.     sSQL = sSQL & "WHERE ((" & GCriteria & ")"
  37.  
  38.     Form_frmInput.RecordSource = "select * from Entry where " & GCriteria
  39.  
  40.     'I removed the line below since I used it when I did a search where user would choose the field to search and type in a single keyword but I dont know how this code have to be changed for multiple search results
  41.  
  42.     'Form_frmInput.Caption = "Entry (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
  43.  
  44.     'Close frmSearch
  45.     DoCmd.Close acForm, "frmSearch"
  46.  
  47.     MsgBox "Results have been filtered."
  48.  
  49.     End If
  50.  
  51. End Sub
** ow and yes, the field where the economic sector is stored is named under "EconomicSector" and yes I did not change any field above to represent this yet.

** optSearch is the option group frame's name
Jun 9 '08 #16

NeoPa
Expert Mod 15k+
P: 31,489
Shan,

I didn't mean you to post this one all again. I'm sorry you went to that extra trouble.
Your first attempt, though a little unorthodox, was nevertheless very clear, so didn't need doing again. I was simply requesting you use the tags next time.

Anyway, what I did want to see posted was the values in the two controls (Me.txtSearchField1 and Me.optSearch) that were passed to the function GetResults() when the code crashed as displayed in post #13.

Knowing what's in the parameters of a function is often critical to finding what's gone wrong.
Jun 9 '08 #17

P: 20
Shan,

I didn't mean you to post this one all again. I'm sorry you went to that extra trouble.
Your first attempt, though a little unorthodox, was nevertheless very clear, so didn't need doing again. I was simply requesting you use the tags next time.

Anyway, what I did want to see posted was the values in the two controls (Me.txtSearchField1 and Me.optSearch) that were passed to the function GetResults() when the code crashed as displayed in post #13.

Knowing what's in the parameters of a function is often critical to finding what's gone wrong.
:o I feel dumb. The only ways I know to monitor the value is by posting a msg box before the error message but it didnt work since it didn't even compile?!

Other method is by adding a watch and it said "field is empty"

Or do you mean what did I type in the box?

Jun 9 '08 #18

NeoPa
Expert Mod 15k+
P: 31,489
Right, it seems from what you said that we have a bunch of confusion here. The first thing to understand is that you should ALWAYS compile the code before asking questions about it. Not doing so will just confuse everyone, as it would make so little sense to post a question about a situation where the code doesn't even compile. The experts, knowing this very well, will assume that you are talking about a compiled project so if it isn't, they will already be off track. If you have problems compiling the code, that is another matter. We can help with that, but then it's a language question rather than a logic one.

MsgBox() is a fine method, but Debug.Print works too (output goes to the Immediate Pane - See Debugging in VBA). A picture of the situation on the form also works.

However, from what you say, I suspect we're not looking at the picture of what actually happened when the error message occurred (this is extremely important). To make it easier, just run the project (after compiling successfully of course) and when it asks you whether you want to End or Debug, select Debug. From here go to the Immediate Pane (Ctrl-G) and type in the following code verbatim :
Expand|Select|Wrap|Line Numbers
  1. ?"txtSearchField1='" & Me.txtSearchField1 & "' & optSearch='" & Me.optSearch & "'"
Lastly, post the results in here for us to see :)
Jun 9 '08 #19

P: 20
Ok before doing that...it is a compile error (the error is shown above in the picture I posted) I also suspect/found that it could be because of this

Expand|Select|Wrap|Line Numbers
  1. Public Function GetResults(strPassed As String, blnExactMatch As Boolean) As String
That ^ is the function title

This is the function calling part

Expand|Select|Wrap|Line Numbers
  1.  GetResults(Me.txtSearchField2, "[Author]", Me.optSearch)
See how we are sending 3 values but the function only got 2? Of course modifying this means editing the function itself :o

When I try to compile it says

"Compile Error

Wrong number of arguments or invalid property assignment"
Jun 9 '08 #20

Megalog
Expert 100+
P: 378
Hey Nik, sorry I havent had much time to revisit this.

Go find my posting (edit: Post #7) that has the code starting with this in line 1:

Expand|Select|Wrap|Line Numbers
  1. Public Function GetResults(strPassed As String, strFieldName As String, blnExactMatch As Boolean) As String
You're running part of the old code, along with the new.. Your project wont compile because you're referring to strFieldName in your code, but not in the function.
Jun 9 '08 #21

P: 20
Wowies :( told you I am dumb lol

One last question,

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSea_Click()
  2.  
  3.     If IsNull(optSearch) Then
  4.     MsgBox "You must select one of the option boxes to define your search!"
  5.     End If
  6.  
  7.     If Not IsNull(txtSearchField1) Then
  8.  
  9.         GCriteria1 = GetResults(Me.txtSearchField1, "[GeographicArea]", Me.optSearch)
  10.         GCriteria = GCriteria1
  11.  
  12.     End If
  13.  
  14.     If Not IsNull(txtSearchField2) Then
  15.         GCriteria2 = GetResults(Me.txtSearchField2, "[Author]", Me.optSearch)
  16.  
  17.         If GCriteria = "" Then
  18.            GCriteria = GCriteria2
  19.  
  20.         Else
  21.               GCriteria = GCriteria & " AND " & GCriteria2
  22.         End If
  23.      End If
  24.  
  25.     If Not IsNull(cboSearchField1) Then
  26.         GCriteria3 = GetResults(cboSearchField1.Value, "[EconomicSector]", Me.optSearch)
  27.  
  28.             If GCriteria = "" Then
  29.                GCriteria = GCriteria3
  30.  
  31.            Else
  32.                    MsgBox "GCriteria" & GCriteria
  33.                    GCriteria = GCriteria & " AND " & GCriteria3
  34.            End If
  35.     End If
  36.  
  37.     sSQL = sSQL & "WHERE ((" & GCriteria & ")"
  38.  
  39.     Form_frmInput.RecordSource = "select * from Entry where " & GCriteria
  40.  
  41.     Form_frmInput.Caption = "Entry (" & cboSearchField1.Value & " contains '*" & txtSearchField1 & "*')"
  42.  
  43.     'Close frmSearch
  44.     DoCmd.Close acForm, "frmSearch"
  45.  
  46.     MsgBox "Results have been filtered."
  47.  
  48. End Sub
Now the bolded part is from my previous code (that I modified)

That code used to do this: I would select a field from a drop down menu, say Author and type a string in another text box (Author name) and it would search and filter them out on back to the input form. However now on this line we got only 2 of 3 criteria listed.

1. Would that still work or it would only display records found with those 2 criteria only?

2. When I did the search with 1 of 3 fields empty, it returned empty record (where as the search did work when all 3 fields are full) i do not want to force the user with a text msg to enter all of the fields so is there any way? (possibly an if-else statement?) thanks thanks and thanks (glad it works up to this =)
Jun 9 '08 #22

NeoPa
Expert Mod 15k+
P: 31,489
It seems that the code should handle that situation Shan. I haven't tested it exactly, but it seems to have the relevant checks in place.

More to the point :- Have YOU tested it?

If you find it doesn't work as intended then explain what is happening and we will see what we can find out for you. If you haven't tested it yet then we needn't waste our time until you do.
Jun 10 '08 #23

P: 20
Ok I've tested it yesterday but did not write down anything so I did an extensive test writing down everything. Results below

The search did not work at all or only worked in certain instances when doing single key worded search (chose from the option group)

Instances it worked are

- when only an economic sector is chosen from drop down menu (and other 2 fields are left behind)

Other instances it did not work (as in either returned 1 record or no record at all)

The multiple keyword search is working like a charm on all cases.

Here is the database for an idea [I have multiple fields in a record, say for country i would have

spain, canada, united states

also would be doing the search in this way

united states, canada


http://rapidshare.com/files/121441418/Experimental_Database.mdb

Also, can i just remove the single keyword search...when i enter a single keyword in the multiple keyword entry, for example

author: patterson
country: canada

it does brings back results...where as when i enter that under the single keyword search, it wouldnt!
Jun 10 '08 #24

Megalog
Expert 100+
P: 378
Interesting..
I'm sorry I havent had much time to finish up this thread, I will grab that database tonight at home and walk through it ok?
Hang on there =)
Jun 10 '08 #25

P: 20
Interesting..
I'm sorry I havent had much time to finish up this thread, I will grab that database tonight at home and walk through it ok?
Hang on there =)

absolutely no problem. I ll hang here for years as I know the value of this free help out there.
Jun 10 '08 #26

NeoPa
Expert Mod 15k+
P: 31,489
Shan, as a quick FYI, you can attach databases to your posts if you need to. We would ask that the database be compacted and as small as possible anyway, as well as Zipped (or other compression).

To do this (after preparing the file) simply add a post; edit the post; scroll down to the attachment properties area and make the relevant selections.
Jun 10 '08 #27

Megalog
Expert 100+
P: 378
Ok, first off.. I found an error.. I missed the semicolon at the end of the sql string. =)
So, this line should be:

Expand|Select|Wrap|Line Numbers
  1. Form_frmInput.RecordSource = "select * from Entry where " & GCriteria & ";"
Now, to answer some of your questions you asked earlier:

Also, can i just remove the single keyword search...when i enter a single keyword in the multiple keyword entry, for example

author: patterson
country: canada

it does brings back results...where as when i enter that under the single keyword search, it wouldnt!
You need to change the wording on your search form, from "Multiple/Single", to "Contains/Exact Match", because that is what they determine and it's misleading you. The reason why your quoted example does not work when you select 'single', is because it's trying to exactly match "patterson" to what is in the field. Looking through the table, "patterson" never appears by itself.. thus, all records are filtered out.

To prove my point, I removed everyone else from one record, and only assigned "patterson", and ran the search with 'single' selected, and it brought back that record. I then ran the search for "patterson" with 'Multiple' (which is really 'Contains', remember...) and it brought back every record that had Patterson listed as an author, regardless of whether there were multiple authors or not.

So, the only way you are determining if you're searching for Multiple or Single matches, is if you type in more than one set of criteria to search for.. The function handles the rest.

Now, one thing you -could- do, is put boolean controls next to every text box, and independantly control whether criteria Contains or Exactly matches. That way you could, for example, find anything that contains "Patterson" as an Author, but in only countries that match "Canada" only. You can get as precise as you want with this.

Now the bolded part is from my previous code (that I modified)

That code used to do this: I would select a field from a drop down menu, say Author and type a string in another text box (Author name) and it would search and filter them out on back to the input form. However now on this line we got only 2 of 3 criteria listed.

1. Would that still work or it would only display records found with those 2 criteria only?
The code you're talking about is this:
Expand|Select|Wrap|Line Numbers
  1. Form_frmInput.Caption = "Entry (" & cboSearchField1.Value & " contains '*" & txtSearchField1 & "*')"
If you look at this code, it's not doing what you think it is. It's giving you the value of the first box, and saying you're searching for entries in that field, that contain the values of the 2nd search box.
I re-did this caption code, although it's a bit excessive.. This will basically string together a new caption that tells the end user exactly what the form is filtering, by:
1> checking to see if a field has data entered
2> extract the label caption text
3> determine if the operation is a Contains or Exact match
4> extract the search field criteria text

Expand|Select|Wrap|Line Numbers
  1. Form_frmInput.Caption = "Entry (" & IIf(IsNull(cboSearchField1.Value), "", lblcboSearchField1.Caption & _
  2.                         IIf(Me.optSearch, " Contains ", " Exactly Matches ") & "'" & cboSearchField1.Value & "'") & _
  3.                         IIf(IsNull(txtSearchField1.Value), "", ", " & lbltxtSearchField1.Caption & _
  4.                         IIf(Me.optSearch, " Contains ", " Exactly Matches ") & "'" & txtSearchField1.Value & "'") & _
  5.                         IIf(IsNull(txtSearchField2.Value), "", ", " & lbltxtSearchField2.Caption & _
  6.                         IIf(Me.optSearch, " Contains ", " Exactly Matches ") & "'" & txtSearchField2.Value & "'") & ")"
If you decide to use this, you'll have to rename your label controls to what I used above, or change it in the code to match what you're using.

Or, you can just grab the attached database where I did all these fixes for you.

Enjoy =)
Attached Files
File Type: zip Experimental_Database.zip (111.3 KB, 98 views)
Jun 11 '08 #28

NeoPa
Expert Mod 15k+
P: 31,489
Nice work MegaLog :)

I find myself explaining so frequently nowadays, that most problems arise from not thinking through clearly what is actually required before trying to design a solution.

@Shan, you need to take this point on-board or you will continue to struggle.

PS. The ";" is generally not required in SQL within Access. Some people like to include it anyway, but it will work equally well without.
Jun 11 '08 #29

P: 20
Thanks Neo for the attachment advice and yes I should've planned.

I am currently pursuing my CA designation but used to be in computer eng for 2 years (and it kinda sucked) so I thought I could help a friend of mine out with this while learning something myself that maybe useful. Its just bad planning and I did not get the whole planned mapped from my friend either. Its just improvise-as-you-go plan but I do now understand that I need to write down what I need at the end and what steps I need to take.

For example, I thought this search would be super easy...never know this many problems would pop up!!

and a big big big huge thanks to Mega!!!! I really appreciate your time. Let me know in which way I can contribute to this forum (whether donation or just helping others I can do both)

Thank you again and I will keep you guys updated with the development of this project in this thread (by posting pictures) and will give a copy of the database so that you guys can help others with it, etc...this is something I did last night.

Jun 11 '08 #30

P: 20
aww shucks :(

There is an error :( The debug line is pointed in the picture



I chose

- Return all records which contain my criteria
Economic Sector: metals and minerals
Country: Canada
Author: Patterson

and this is from the experimental one (not the one i m doing on the side)
Jun 11 '08 #31

Megalog
Expert 100+
P: 378
aww shucks :(
There is an error :( The debug line is pointed in the picture
Hmm.. it worked for me last night. That line is just requerying the data on the input form after running the search.
Did you rename the input form from frmInput, to Form_frmInput ? The preceding lines before it use Form_frminput.. and the 2 statements afterwards are using just frmInput.
Jun 11 '08 #32

P: 20
Hmm.. it worked for me last night. That line is just requerying the data on the input form after running the search.
Did you rename the input form from frmInput, to Form_frmInput ? The preceding lines before it use Form_frminput.. and the 2 statements afterwards are using just frmInput.

Nope not at all, I just downloaded the one you posted and ran some searches...When I first encountered the error, I posted it above.

After that I kept looking at it and noticed it could be because, everywhere else its mentioned as "Form_frmInput" so why not here and changed it to that thinking this might be the error (as you said above) but still gave me this one

Jun 11 '08 #33

Megalog
Expert 100+
P: 378
Well my guess is that the input form isnt even open.
Make sure you open that form first, and then try the search.
Jun 11 '08 #34

P: 20
Well my guess is that the input form isnt even open.
Make sure you open that form first, and then try the search.
Yep it is open (I dont know if I am asking too much but it is the same database as you posted, you could try it)
Jun 11 '08 #35

P: 20
Nvm solved it

Forms!Form_frmInput.Requery

I just remove the 'Forms!'

so this works fine

Form_frmInput.Requery

Thanks though ;)
Jun 11 '08 #36

Megalog
Expert 100+
P: 378
Nvm solved it

Forms!Form_frmInput.Requery

I just remove the 'Forms!'

so this works fine

Form_frmInput.Requery

Thanks though ;)
That's the weird part.. it worked for me, and I did try it =)
Have fun!
-Mega
Jun 11 '08 #37

NeoPa
Expert Mod 15k+
P: 31,489
Nvm solved it

Forms!Form_frmInput.Requery

I just remove the 'Forms!'

so this works fine

Form_frmInput.Requery

Thanks though ;)
This is a complicated one.

The underlying (actual) name for the class is Form_frmInput. Forms are strange classes in that they (typically) only have the one instance. This object will be named Form_frmInput (I believe other instances, if used, might have variations of this).

However, most objects also have a Name attribute. The Name attribute (frmInput) is one of the ways that a form can be accessed from within the Forms collection. I wasn't aware that the underlying name (Form_frmInput) could be used as an index into the Forms collection (It certainly can't in Access 2000 but maybe in later versions...?).

Essentially then it is one method OR the other for most portable code.
Jun 12 '08 #38

P: 20
Yea now that you said it, I guess this would work too (didnt try it though)

Forms!frmInput.Requery

I also got an off topic (well not really) question:

When I do the searches (all works fine...as in no debug errors etc) I noticed this odd phenomena:

Note: I've also added ApproachtoCSR as another search term.

So lets say I type in "United States" (note the double worded string) in Geographic Area and it brings all instances of United States whether the record have United States in the middle, front or end or even as a single word of it (all cases are below)

Canada, United States, England
United States, England, Angola
Cuba, England, United States
United States

However, in Approach to CSR field, if I type ANY words...the search only returns instances where the word is found ANYWHERE except as the first [say I typed in United States...taking as an example only]

Then it would return

Canada, United States, England
United States, England, Angola << Not this
Cuba, England, United States
United States << Not this

But then again, I did not type in "United States" I chose a word from the list box to do my experiment.

Please see attached database. Thanks!
Attached Files
File Type: zip Experimental Database.zip (174.5 KB, 110 views)
Jun 12 '08 #39

NeoPa
Expert Mod 15k+
P: 31,489
Shan,

I didn't mean to give the impression earlier that posting databases was a good idea, or that it should be used instead of explaining the question properly.

We need to see the code that you use to set up the search that is going wrong (and only that). Remember this is a forum, and not a data depository.
Jun 12 '08 #40

P: 20
Oops sorry!

Ok I found out the problem and solved it!!! It was caused by a stupid space [ ]

Sometimes I just can't believe such small things would cause big problems :o
Jun 12 '08 #41

Megalog
Expert 100+
P: 378
Oops sorry!

Ok I found out the problem and solved it!!! It was caused by a stupid space [ ]

Sometimes I just can't believe such small things would cause big problems :o
Dont forget those stupid quotes, commas, brackets, and parenthesis.... ;)

Remember, if something is going funky when it used to work just fine in other cases.. it's probably a syntax or usage error of some sort like that.
Jun 13 '08 #42

Post your reply

Sign in to post your reply or Sign up for a free account.