Doing an "Or" search using criteria from a text box

[Robertf987]

Hi,
Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000.

What I want is to be able to do a search, taking it's criteria fro a text box on a form. This bit is easy, done it a jazillion times. But I want it to be an "Or" search. If I type in it "Cakes, Cheese", I don't want it to bring up results that contain cakes AND cheese, I want it to bring up all results that contain either cakes OR cheese. Now, I can do this if I put what I want into the query. So if I put in the query:

Like "Cake" Or "Cheese"

then that works absolutley fine. However, if I link the query to a text box and in the text box I type in: " "Cake" Or "Cheese", that doesn't work.

Now then. What I have, is a database, it stores the learning resources we have, books, videos, etc. I have a search form. On it is a list box and a text box. The list box contains a list of subjects such as Careers, Health, Sex Education, etc. When you click on a word in the list box, it appears in the text box, seperated by a comma. I then have a query based on the contents of that text box where the words appear. Now, that works absolutley fine as an "And" query. If I only select one subject to search by, it will show all resources that relate to that subject. If I select more than one subject, it will show me all the resources that have both subjects. (i.e. if I select Health and Wellbeing, it will show be all the resources that are both health AND wellbeing.) But I want it to show all of the resources that have EITHER of the result words, not BOTH of them. Like I said, I can do this if I hotwire it into the query, but I can't do it if the query gets the search criteria from a text box.

This is what I have as the query:

Like "*" & [Forms]![SearchSubjects]![Subject] & "*"

and this works fine as an 'And' search. Now for the record, in that text box, the words don't have to be seperated by a comma. I can insert anything in between the words, for example, if health and wellbeing were clicked in the list box, I could make it appear in the text box as: " "Health" Or "Wellbeing" ". Done that and it still doesn't work.

If I put the query as:

 Like "*" & "Wellbeing" & "*" Or "*" & "Health" & "*"

that's okay. However, if I put the query to only get it's criteria from a text box, (i.e. make the query: [Forms]![SearchSubjects]![Subject]) then put the same expression into the text box that worked in the query, that won't work.

So to cut all this down now into a one sentence evaluation, does anybody out there know how to do an "Or" search which gets it's criteria from a text box? The layout of the words in the text box can be modified to automatically put in quotation marks, "OR"s or whatever else is needed between the search words.

I hope all of this is understandable, and I will really, truly be greateful for any help anybody can give.

[ADezii]

Hi,
Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000.

What I want is to be able to do a search, taking it's criteria fro a text box on a form. This bit is easy, done it a jazillion times. But I want it to be an "Or" search. If I type in it "Cakes, Cheese", I don't want it to bring up results that contain cakes AND cheese, I want it to bring up all results that contain either cakes OR cheese. Now, I can do this if I put what I want into the query. So if I put in the query:

Like "Cake" Or "Cheese"

then that works absolutley fine. However, if I link the query to a text box and in the text box I type in: " "Cake" Or "Cheese", that doesn't work.

Now then. What I have, is a database, it stores the learning resources we have, books, videos, etc. I have a search form. On it is a list box and a text box. The list box contains a list of subjects such as Careers, Health, Sex Education, etc. When you click on a word in the list box, it appears in the text box, seperated by a comma. I then have a query based on the contents of that text box where the words appear. Now, that works absolutley fine as an "And" query. If I only select one subject to search by, it will show all resources that relate to that subject. If I select more than one subject, it will show me all the resources that have both subjects. (i.e. if I select Health and Wellbeing, it will show be all the resources that are both health AND wellbeing.) But I want it to show all of the resources that have EITHER of the result words, not BOTH of them. Like I said, I can do this if I hotwire it into the query, but I can't do it if the query gets the search criteria from a text box.

This is what I have as the query:

Like "*" & [Forms]![SearchSubjects]![Subject] & "*"

and this works fine as an 'And' search. Now for the record, in that text box, the words don't have to be seperated by a comma. I can insert anything in between the words, for example, if health and wellbeing were clicked in the list box, I could make it appear in the text box as: " "Health" Or "Wellbeing" ". Done that and it still doesn't work.

If I put the query as:

 Like "*" & "Wellbeing" & "*" Or "*" & "Health" & "*"

that's okay. However, if I put the query to only get it's criteria from a text box, (i.e. make the query: [Forms]![SearchSubjects]![Subject]) then put the same expression into the text box that worked in the query, that won't work.

So to cut all this down now into a one sentence evaluation, does anybody out there know how to do an "Or" search which gets it's criteria from a text box? The layout of the words in the text box can be modified to automatically put in quotation marks, "OR"s or whatever else is needed between the search words.

I hope all of this is understandable, and I will really, truly be greateful for any help anybody can give.

It's actually a lot simpler that you realize. I ran parallel code to create a SQL Statement that would be the Record Source for my Test Form. The code has been tested and is operational, but first a few Assumptions:

1. The Text Box containing the multiple criteria is named txtSearch.
2. I ran my tests on a Table named Employees.
3. The multiple ORed criteria were for a [LastName] Field in the Employees Table.
4. Criteria contained in txtSearch were Delimited by commas with no spaces in between, namely:
   [CODE=text]Criteria1,Crite ria2,Criteria3, Criteria4,Crite ria5, etc.......[/CODE]
5. Make your own substitutions wherever appropriate.
   [CODE=vb]
   Dim varCriteria As Variant
   Dim intCounter As Integer
   Dim strSQL_1 As String
   Dim strBuildString As String
   
   strSQL_1 = "Select * From Employees Where "
   
   If Not IsNull(Me![txtSearch]) Then
   varCriteria = Split(Me![txtSearch], ",")
   
   For intCounter = LBound(varCrite ria) To UBound(varCrite ria)
   strBuildString = strBuildString & "[LastName] Like '*" & varCriteria(int Counter) & "*' OR "
   Next
   
   Me.RecordSource = strSQL_1 & Left$(strBuildS tring, Len(strBuildStr ing) - 4)
   End If[/CODE]
6. If you need explanations on anything, other Members or myself would be glad to help you.

[Robertf987]

Thank you for your very quick response. I've tried to implement your code, but I am having a little trouble. At the end of your code, where it says Me.Recordsource = strsql_1, I need it to implement the sql on another form, not the current. Easy enough to do, I just replaced 'Me' with the form name. I thought that would work. But now on that line I get "Run-time error '424' Object Required".

I have two forms: one for doing the searching from (that contains the text box to search from) and another form to show the results. I put the code into a button on the search form that contains the text box. The text box is called "Subject". The form that shows the results is called "SearchResults" . The table taht cotains all the data is called "Resources" , and the field we want to get results from in this table is "Subject".

Here's what I did with your code (I put it in a on click procedure for a button on the search form that contains the text box):

Private Sub BtnSearch_Click()
Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String
Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![Subject]) Then
  varCriteria = Split(Me![Subject], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[Subject] Like '*" & varCriteria(intCounter) & "*' OR "
  Next
 
  DoCmd.OpenForm "SearchResults"
  SearchResults.RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If
End Sub

However, the last line of code, the 'SearchResults. RecordSource = strSQL_1.....' is where it stops and I get the error "Run-time error '424' Object Required".

Any suggestions? (Sorry to do this again! And thanks loads for the help so far.)

[ADezii]

Thank you for your very quick response. I've tried to implement your code, but I am having a little trouble. At the end of your code, where it says Me.Recordsource = strsql_1, I need it to implement the sql on another form, not the current. Easy enough to do, I just replaced 'Me' with the form name. I thought that would work. But now on that line I get "Run-time error '424' Object Required".

I have two forms: one for doing the searching from (that contains the text box to search from) and another form to show the results. I put the code into a button on the search form that contains the text box. The text box is called "Subject". The form that shows the results is called "SearchResults" . The table taht cotains all the data is called "Resources" , and the field we want to get results from in this table is "Subject".

Here's what I did with your code (I put it in a on click procedure for a button on the search form that contains the text box):

Private Sub BtnSearch_Click()
Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String
Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![Subject]) Then
  varCriteria = Split(Me![Subject], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[Subject] Like '*" & varCriteria(intCounter) & "*' OR "
  Next
 
  DoCmd.OpenForm "SearchResults"
  SearchResults.RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If
End Sub

However, the last line of code, the 'SearchResults. RecordSource = strSQL_1.....' is where it stops and I get the error "Run-time error '424' Object Required".

Any suggestions? (Sorry to do this again! And thanks loads for the help so far.)

You need a Fully Qualified Path to the SearchResults Form, namely:
[CODE=vb]
Forms!SearchRes ults.RecordSour ce = strSQL_1 & Left$(strBuildS tring, Len(strBuildStr ing) - 4)[/CODE]

[Robertf987]

Thank you, all working fine now. I should have really known that... but I'm still learning (as we all are!).

Thanks for all your help, it's much appreciated. Will hopefully have this database implemented and in use next week, just got one more major function to do, then have to make it look professional (that's the stuff I'm good at =P, the easy stuff!)

[ADezii]

Thank you, all working fine now. I should have really known that... but I'm still learning (as we all are!).

Thanks for all your help, it's much appreciated. Will hopefully have this database implemented and in use next week, just got one more major function to do, then have to make it look professional (that's the stuff I'm good at =P, the easy stuff!)

Anything that we can help you with - that's why we're all here.

[Robertf987]

Okay, another question. This is the very last thing that needs doing on this database. I wasn't sure whether to start a new thread or add it to this one. As it's linked with my previous post I thought I would add it to here. I have that previous bit all working - the SQL is built into a macro and it makes all the data appear in a form. Next bit, probably easy again, but printing. I don't want to print the form, as it will use too much ink. I want to make a report of the data on the form. However, as the data comes from SQL that is in VBA, how do I transfer the data from the form into a report, if the data is aquired in VBA and not a table or query? Is there some way of getting the data from the form directly into a report (I had tried this, but it didn't work. It only showed the first record in the report, not all of them in the datasheet form.) Or do I have to modify the code and re-query it and build that into a report somehow?

I may figure it out by time I get a reply, but for now I'm not sure how to do it so I'm asking for help. Again, I would be immensely greateful for any help. Many thanks.

[ADezii]

Okay, another question. This is the very last thing that needs doing on this database. I wasn't sure whether to start a new thread or add it to this one. As it's linked with my previous post I thought I would add it to here. I have that previous bit all working - the SQL is built into a macro and it makes all the data appear in a form. Next bit, probably easy again, but printing. I don't want to print the form, as it will use too much ink. I want to make a report of the data on the form. However, as the data comes from SQL that is in VBA, how do I transfer the data from the form into a report, if the data is aquired in VBA and not a table or query? Is there some way of getting the data from the form directly into a report (I had tried this, but it didn't work. It only showed the first record in the report, not all of them in the datasheet form.) Or do I have to modify the code and re-query it and build that into a report somehow?

I may figure it out by time I get a reply, but for now I'm not sure how to do it so I'm asking for help. Again, I would be immensely greateful for any help. Many thanks.

In the Open() Event of the Report, set its RecordSource Property equal to the same SQL Statement that is the Record Source for the Form:
[CODE=vb]
Private Sub Report_Open(Can cel As Integer)
Me.RecordSource = "<SQL Statement>;"
End Sub[/CODE]
NOTE: When the SQL Statement is initially created, you could assign it to a Public (Global) Variable, where it could then be used in the Report's Open() Event:
[CODE=vb]
Private Sub Report_Open(Can cel As Integer)
Me.RecordSource = gPublicVariable
End Sub[/CODE]

[Robertf987]

Okay, I'm going to be really annoying again and ask for yet more help. I'm having problems with applying the SQL code to the report.

I tried putting the code into the onOpen control of the report. Here's the code I used:

Private Sub Report_Open(Cancel As Integer)
Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String

Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![SearchedSubjects]) Then
  varCriteria = Split(Me![SearchedSubjects], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[searchedsubjects] Like '*" & varCriteria(intCounter) & "*' OR "
  Next

  Me!RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If
End Sub

This always stops on the ‘varCriteria = Split’ line, and I get the message:
“Run-Time error ‘2427’: You entered an expression that has no value.’

Okay. Well, I decided to put the code into a button on the 'Print' button on the form. So here's what I used:

Private Sub BtnPrint_Click()

Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String

Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![SearchedSubjects]) Then
  varCriteria = Split(Me![SearchedSubjects], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[searchedsubjects] Like '*" & varCriteria(intCounter) & "*' OR "
  Next
 
  DoCmd.OpenReport "SubjectSearchResults", acViewPreview

  Reports!SubjectSearchResults.RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If

End Sub

Same code, just with a few modifications. Anyway, when I run this, it stops on the last line, 'Reports!Subjec tSearchResults. RecordSource = ...' and I get the message:
‘Run-time error ‘2191’: You can’t set the Record Source property after printing has started.’
Now, I know what both messages mean. The can't set record source after printing has started means that it's started printing before the query has been applied. However, if I move the open report command to later, then it's not open in the first place to be able to apply the query to. The previous one, expression has no value, is I think becasue reports won't let text boxes have a 'value' for some reason. When I've tried to do very simple stuff before on a report, like.. textbox1.value = textbox2.value in vba, it won't take it. It won't let a text box have a .value command.

However, I don't know what to do next. As far as I can see, they are both correct. But that's why I'm here, becasue I don't know what to do.

Any help would be greatly appreciated. The good thing is, with all of this, I'm learning! I *think* this should be the last thing I'll need help with (on this database anyway, and I'm not planning on making any more any time soon! Although I do enjoy it, gets the brain working.)

[ADezii]

Okay, I'm going to be really annoying again and ask for yet more help. I'm having problems with applying the SQL code to the report.

I tried putting the code into the onOpen control of the report. Here's the code I used:

Private Sub Report_Open(Cancel As Integer)
Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String

Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![SearchedSubjects]) Then
  varCriteria = Split(Me![SearchedSubjects], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[searchedsubjects] Like '*" & varCriteria(intCounter) & "*' OR "
  Next

  Me!RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If
End Sub

This always stops on the ‘varCriteria = Split’ line, and I get the message:
“Run-Time error ‘2427’: You entered an expression that has no value.’

Okay. Well, I decided to put the code into a button on the 'Print' button on the form. So here's what I used:

Private Sub BtnPrint_Click()

Dim varCriteria As Variant
Dim intCounter As Integer
Dim strSQL_1 As String

Dim strBuildString As String
 
strSQL_1 = "Select * From Resources Where "
 
If Not IsNull(Me![SearchedSubjects]) Then
  varCriteria = Split(Me![SearchedSubjects], ",")
 
  For intCounter = LBound(varCriteria) To UBound(varCriteria)
    strBuildString = strBuildString & "[searchedsubjects] Like '*" & varCriteria(intCounter) & "*' OR "
  Next
 
  DoCmd.OpenReport "SubjectSearchResults", acViewPreview

  Reports!SubjectSearchResults.RecordSource = strSQL_1 & Left$(strBuildString, Len(strBuildString) - 4)

End If

End Sub

Same code, just with a few modifications. Anyway, when I run this, it stops on the last line, 'Reports!Subjec tSearchResults. RecordSource = ...' and I get the message:
‘Run-time error ‘2191’: You can’t set the Record Source property after printing has started.’
Now, I know what both messages mean. The can't set record source after printing has started means that it's started printing before the query has been applied. However, if I move the open report command to later, then it's not open in the first place to be able to apply the query to. The previous one, expression has no value, is I think becasue reports won't let text boxes have a 'value' for some reason. When I've tried to do very simple stuff before on a report, like.. textbox1.value = textbox2.value in vba, it won't take it. It won't let a text box have a .value command.

However, I don't know what to do next. As far as I can see, they are both correct. But that's why I'm here, becasue I don't know what to do.

Any help would be greatly appreciated. The good thing is, with all of this, I'm learning! I *think* this should be the last thing I'll need help with (on this database anyway, and I'm not planning on making any more any time soon! Although I do enjoy it, gets the brain working.)

Is your Search String, namely strBuildString, created prior to opening the Report? I'm asssuming it is and that the Report is being opened from a Command Button on the Form.

[Robertf987]

Is your Search String, namely strBuildString, created prior to opening the Report? I'm asssuming it is and that the Report is being opened from a Command Button on the Form.

Apologies if I'm being simple here, I'm going to try and explain.

The text string itself is created on the form. It's in a text box. It's a list of words seperated by commas, and a search is done using this criteria. You helped me with the VBA and SQL to do that. The code works fine, and brings up the results. Now I want to be able to print the results shown in the form in a report, as printing the form itself uses too much ink and isn't professional. So I'm trying to implement the same code into a report. That much I think you probably already gathered. Right. The code has already been implemented on the form. So... yes the search string is created prior to opening the report. However, the search string needs to be cut up to be able to be used. This is done again either in the button that opens/prints the form, or in the onOpen of the report. In both of the codes (in Button click or open form), the string is split up and the query implemented before the form is printed. I think. Ohh dear this is a bit too complicated for my tiny brain.

[ADezii]

Apologies if I'm being simple here, I'm going to try and explain.

The text string itself is created on the form. It's in a text box. It's a list of words seperated by commas, and a search is done using this criteria. You helped me with the VBA and SQL to do that. The code works fine, and brings up the results. Now I want to be able to print the results shown in the form in a report, as printing the form itself uses too much ink and isn't professional. So I'm trying to implement the same code into a report. That much I think you probably already gathered. Right. The code has already been implemented on the form. So... yes the search string is created prior to opening the report. However, the search string needs to be cut up to be able to be used. This is done again either in the button that opens/prints the form, or in the onOpen of the report. In both of the codes (in Button click or open form), the string is split up and the query implemented before the form is printed. I think. Ohh dear this is a bit too complicated for my tiny brain.

1. Delete the line in the Form's Code Module where the Build String Variable is declared, namely:
   [CODE=vb]Dim strBuildString As String 'DELETE this line[/CODE]
2. Make a New declaration in a Standard Code Module in the General Declarations Section. The below listed Declaration will now make strBuildString a Public (Global) Variable which means that it can be accessed anywhere within your Application.
   [CODE=vb]Public strBuildString As String[/CODE]
3. Place the following line of code in your Report's Open() Event:
   [CODE=vb]
   Private Sub Report_Open(Can cel As Integer)
   Me.RecordSource = strBuildString
   End Sub[/CODE]

However, the search string needs to be cut up to be able to be used.

Sorry, but I have no idea what you mean by this. Cut up how?

[Robertf987]

Okay, it's all done now, totally completed you'll be pleased to know. I actually had to make the StrSQL_1 the global variable and put that into the report. All tested though and all working fine, thank you SO much for all the help.

What I meant when I said the string needed sutting up, I probably didn't articulate it very well, but what I meant was the string is a list of words seperated by commas, and the commas needed removing and OR putting between them for doing the search. The code did this.

Thanks again for all the help, it's incredibly appreciated. The database is now all working, tested it pretty thoroughly and can't find a problem. I'll take it into the office tomorrow and it will then be in use. (And 10 minutes later be told it's not working!) It was only a small and fairly simple database, I don't see any problems arising. Thanks for all your help and advice, and I hope I've not been too frustrating!

[ADezii]

Okay, it's all done now, totally completed you'll be pleased to know. I actually had to make the StrSQL_1 the global variable and put that into the report. All tested though and all working fine, thank you SO much for all the help.

What I meant when I said the string needed sutting up, I probably didn't articulate it very well, but what I meant was the string is a list of words seperated by commas, and the commas needed removing and OR putting between them for doing the search. The code did this.

Thanks again for all the help, it's incredibly appreciated. The database is now all working, tested it pretty thoroughly and can't find a problem. I'll take it into the office tomorrow and it will then be in use. (And 10 minutes later be told it's not working!) It was only a small and fairly simple database, I don't see any problems arising. Thanks for all your help and advice, and I hope I've not been too frustrating!

Glad I was able to help you, Robertf987. If any other problems surface, give us a call.