Searching multiple text fields

BradleyS

Registered User.
Local time
Today, 20:11
Joined
Jun 29, 2003
Messages
16
Hi

I am new to Access and this may seem a very simple thing to some of you, but I am trying to build a simple search engine for my database. “Well simple to use anyway”.

I have 5 columns in my database with 1000s of rows of data.

I have built a form called “Form1” with 5 text fields (unbound) called “Text1”, “Text2” etc… and 1 command button called “Command1” that will run a Report and display the results of all 5 fields.

All I want to be able to do is type text in ANYONE of the 5 text fields on the form and it produces a report of my findings. The more text fields I use the smaller the report as it narrows down the search.

I just can’t build a query to accomplish this, could anybody guide me in the right direction.

Thanks
 
Some questions first:

(1) would you only have text in one of the search fields at once, or could you have multiple fields filled (and thus multiple searches going on)?

(2) you mention that your 5 form fields are unbound. Do they correspond to 5 different query or table fields? Meaning, are there 5 separate fields in your underlying table or query that you want to search?
 
Hi

Thanks for the prompt reply

(1) Yes you could have multiple fields filled in, so that it would narrow down the results from the table.

(2) Yes I have been trying to use a query to perform this, but I am open to any suggestions.
 
OK, so if your five fields could be filled in like this:
Field1: frog
Field2: lion
Field3: tiger
you'd want to be able to search for any of those 3 words within any of the 5 fields?
 
Well,

The table would be like this;
Column Headers;
Column 1 = Animals
Column 2 = Colour
Column 3 = Location

Data in table;
Row 1 = Cat ---- Black ---- Home
Row 2 = Cat ---- White --- Outside
Row 3 = Dog --- Brown --- Home
Row 4 = Frog --- Green --- Outside

If I wanted to find all the cats, I would just use Field 1
Result: It displays row 1& 2

If I wanted to find all the black cats I would use Field 1 & 2
Result: It displays row 1

If I wanted to find all the outside animals I would use Field 3
Result: It displays row 2 & 4

And so on, it’s probably more like a filtering system.
 
Oh, even easier than I initially thought. By the way, you may want to consider using what they call "cascading" combo boxes to make this filtering easier on your users. It means making the each combo box depend on the previous one. For example, only allowing users to choose "black" or "white" from box 2 when the user has chosen "cat" from box 1 if those are the only valid color choices for cats. But that's lesson number 2.

The easiest way to handle your situation is to simply make your form a "front-end" to your query. IOW, make the query reference the choices you've made in the combo boxes on your form.

Step 1 is to create the three combo boxes on your form and fill them with the possible choices. Make sure you name them something logical. The first box could be called cboAnimal, the second cboColor, etc.... Save that form (I'll assume you will call it frmMain).

Now, create a query that has some fields including the animal, color, and location fields. In the critieria line for each of those fields, reference the form by using an expression like this:
Forms!frmMain!cboAnimal Do something similar for the two other fields in the query. Now, this query will only work properly if the frmMain form is actually open. Save the query. Call it qryMain or something logical.

Now go back to the form. Create a command button to execute your search. The easiest way to display the results is to open the query itself using some code in the Click event of that command button that looks something like this:
DoCmd.OpenQuery "qryMain"

This is the easiest way to get the functionality you want, but there are many potential problems with it. What if the user doesn't make a choice in one of the combo boxes? What if there are no records that match your criteria. It's nice to have an informative message come up and not just an empty query.

Anyway, there are many ways to make this basic solution better. Let me know if you want to go further.
 
Ok

I understand what you mean, but I don’t think I can use a combo box for the first 3 fields because there are thousands of choices. In fact the database holds 47187 rows of data so far and it keeps growing.

The 1st field is the name of the item and it can be a string of different words in the one line. I'm afraid I haven’t been very inventive with naming the field names, this field is called [Text1]

The 2nd and 3rd fields don’t hold as much data but I still wouldn’t want to have to scroll down a 1000 fields. These fields are used to help break down the 1st field, because it can (not always) hold the same data. Called [Text2] and [Text3]. I think you’ve probably got the idea now of my naming convention.

The 4th and 5th fields can most defiantly be combo boxes as they both only hold the letters Y for Yes and N for No. They are used to distinguish whether the item is discontinued or not.

So you see I think the first 3 fields need to be text boxes so that I can type in some words and it refines the search to just show me my criteria.

I can do this with one text box using the query with an unbound text box on the form.

Like “*” & [Forms]![Form1]![Text1]& “*”

But I can’t get the query to use “either or more than” using the other Text fields let alone the combo boxes.

Sorry to be such a pain :(
 
You can use text boxes instead of combo boxes, that's no problem.

I don't get exactly what you mean by:
But I can’t get the query to use “either or more than” using the other Text fields

You might also want to consider using checkboxes for those two text fields that are y/n choices.

You are NOT being a pain! :D I might be out most of the day today, so I won't be able to answer question until late tonight (I'm in New York).
 
Hi

Yes check boxes would be a good idea.

I mean either using one text box or more than one text box to refine the search.

Is it possible to build this in a query or do I have to put it in some VB code?

Did “I’m in New York” mean that you thought I didn’t no where Brooklyn was? And does that mean you have a life outside of this forum? What is the world coming too?

Enjoy the day! Dcx693 and thank you for your time so far
 
I haven't left the house yet, so I can answer one more time. :) I forget that I have Brooklyn in my profile!

Using one or more text boxes in your form for the search just means that you need to use the same number criteria in your query. Just make sure the criteria is listed on the same line so that it becomes an "AND" connection (meaning all of the conditions must be true in order to return records) versus placing them on different lines in the QBE grid (which would make it an "OR" connection). You want all cats AND black AND home, not cats OR black OR home.

This can all go into a query, no need for VB code unless you want to refine the solution.
 
Ok

Hope you had a good day Dcx693, I was at a friends barbecue today and had a few beers.

:eek: I can’t believe I didn’t try that it works fine accept for 2 things.

Text box 2 and 3 have a lot of blanks in there columns within the table and my query code is not picking them up, i.e. if the text field is blank on the form it excludes them from the search, so what should I change in the code to include blanks as well.

The code is as written before

Like “*” & [Forms]![Form1]![Text1]& “*”

Also how do I get the check boxes to work with the y/n’s from the form.
Oh I forgot to mention the y/n columns are text fields in the table and I cannot modify the table as it is linked to a main database, I just use the Linked Table Manager to update from it. So the fields are typed with a Y or N in the columns, I don't know if this makes a difference or not when using check boxes.
 
Last edited:
Yes, I did have a good day thank you. Went hiking in upstate NY with some friends. Very hot, but fun.

OK, the problem you have is that some values are blank. The reason that the Like * statement doesn't work is because, by definition, a blank value is not equal to anything - it's Null. To find values such as those, you can use the IsNull() function. Or as a practical workaround, you can use the Nz() function to replace Null values with something else.

What you should do in your query is use the Nz function. In the field line where you might have a field name like Animal, change the field name to something like this: Animalname:Nz([Animal],"")
What that will do is replace the Null values (the blank values) into zero-length strings. Then, when you run the query to find Like * , the zero-length strings will be included.

As for the checkboxes....since your field contains "Y" or "N", and you can't change that, use a similar technique as above to change the field expression going into the query. Instead of using the Nz() function, use the IIF function like this: IIf([field]="Y",True,False) . Then reference the checkbox fields on your form, which will either be checked (equal to True) or unchecked (equal to False). I haven't actually tried this form of the technique myself, but it should work (in theory!).
 
Great :) That’s it!

However, it has highlighted some table issues. So I wonder if I can get around them, I mentioned earlier that [Text1] holds all the main data. I don’t know if this is asking the impossible but what the hell, if I don’t ask!

Would it be possible to search out any of the words in the first text box, by entering the data in the one form field, i.e. if the first column of data in the table held something like

Row 1 - “Black cat found up a tree”
Row 2 - “White dog found by a tree”

If I typed in the 1st text box all the keywords on the form “Black cat tree” it would display Row 1. If I typed, “found tree” it would find Row 1 & 2.

Also the check box formula worked fine. However, the Form displays the results on a Report, so is it possible to revert the 0,1,-1’s back to the Y/N on the report.

This is amazing, I really need to learn this! :cool: how do you know which formulas to use, is there a book you could recommend?
 
Hi BradleyS

The attached sample db may be what you are looking for. Its another alternative method.

It will require modification to suit your database.

Have fun

Cheers!!!


Lou :)
 

Attachments

Hi Lou

The sample database looks good, but I need something slightly different.

For example; your Subject field holds a string of words.

“Smith family has a baby boy John”
“Smith family John and Jenny”

When I type in “Smith” it finds the 2 rows, but I want to be able to type in any of the words in the Subject field, i.e. “family” or “family baby John”

If I do this it finds nothing?

That’s my quandary!
 
Lou's sample database demonstrates a lot of the techniques you can use to refine your current solution. However, it doesn't address the exact problem you want to solve.

It's a bit more involved than can be handled with your query, unfortunately. You would need to use some VBA code to separate out each word that is delimited by a space character. Then you would need a way to tell Access to look for a record which has "Smith", "family", "baby", and "John" and that those words could occur in any order and anywhere in the record. You'd write some VBA code, stringing along criteria such as this: Like "*Smith*" AND Like "*family*" etc...

If you take a look at how Lou's database strings along criteria from the input fields on his form, you'll see how it's done. He builds a SQL criteria string dynamically. He displays the found records in a subform of the search form. He sets the recordsource of the subform equal to the SQL string that he builds.

You see, queries can be totally represented as Structured Query Language text. Thus the most flexible way to build queries is to build SQL strings yourself, then set the recordsource of a form or subform equal to the SQL string so the results can be viewed.

To answer your other questions, it is possible to convert the 0's and -1's back to Y/N on your report. In your report, use a text field to display the Y/N field, use a formula like this to convert the numbers to "Y" or "N": =IIf([Field],"Y","N") . It's actually even easier if you can settle for displaying the result as "Yes" or "No", just change the field Format property in the field's property sheet to Yes/No.

How do you know which formula to use? You try using some, read up a little, try some more, and so on. Get yourself a good beginning Access book and read it pretty much from cover to cover. Once you've outgrown it, get yourself the Access 2000 (or 2002) Developer Handbook by Ken Getz, Paul Litwin, and Mike Gilbert? (I don't have the book in front of me). Over 1,000 pages of great stuff.
 
Thanks guys......

Hi Beef,

Just thought I would thank you for the samplesearch.zip as I have incorporated ideas from it into my database.

The people on this site are very supportive, I appreciate it.....:D

There is also a lot of information on this site, so I do thorough searches before asking questions, it is great!!!

Robert88
 
Last edited:
Thanks to Beef & dcx693.

The sample provided is proving very useful, thanks!

I've been sorting through a search of "search multiple fields" and this is one post that satnds out so far.

Is there a consensus solution that most seasoned posters here would recommend to use? I know in Access there are many different ways to accomplish the same things, just wondering if one particular one is considered 'the best'.

Thank you!
 
Question to Beef or anyone familiar with this sample (about posted samplesearch.zip db):

Say I wanted an individual input box on the search form to query multiple related fields (say their is a field where order address and also a seperate field where customer address is stored), how would that be done"

Would you simply build a query that pulled all the data from its possible sepereate locations, and then refer to that query result by copying this VBA over and over:

If Me![Phone] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.OrderPhone = """ & AreaCode & """"
End If

If Me![Phone] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.CustomerPhone = """ & AreaCode & """"
End If

Will that work? Where one field [Phone] is the criteria for multiple fields in the search?

Thanks!
 
The above can't be right.

If I ran an if then for each one of the possible matches based on 1 field, wouldn't it only work if the last one listed matched? Otherwise, wouldn't the variable be reset with each step up until the last one that references [Phone]?

I'm sure this is basic stuff here, but I need help with it.

I imagine the proper way to do this would be for the If Me![Phone] statement to include a second set of if/thens that check each possible field from the query.

Am I making any sense?

Thanks.
 

Users who are viewing this thread

Back
Top Bottom