How can i make a search box for all fields in a table?

[Access_noob_]

This is my first project in Access, so this might be a really easy fix, and this might not be the right forum, but any help would be appreciated. I have a database of our inventory and wanted to add a search feature to it. I managed to create a split form with a search box at the top using Apply Filter with the command [Item#] Like "*" & Forms!inventtab!text20 & "*". It would work exactly like I need it to if it could be applied to ALL fields of the table. I only have nine fields that need searched, but it surpasses the character limit of the where condition line when I enter them all like the example. Any way to shorten the SQL so that it fits, or maybe there is something that replaces the first bracket so that it applies to all fields of the table? Thanks in advance!

[ranman256]

You could make a union query.
in it has 9 querys (you only have 9 fields)

select * from tbl where [fld1] =txtbox
union
select * from tbl where [fld2] =txtbox
union
select * from tbl where [fld3] =txtbox

etc..

[ssanfu]

It would work exactly like I need it to if it could be applied to ALL fields of the table. I only have nine fields that need searched,

Why nine fields? This sounds like a non- normalized structure. What are the filed names?

See http://www.allenbrowne.com/ser-62code.html

Normally you should have different field names and search text boxes, but you should be able to use the one search text box and create the filter using the link above.

Be aware of when to use "AND" and "OR"...........

[Missinglinq]

I agree with Steve; if you want to enter one search term and have nine fields searched using it, your database is almost assuredly non-normalized, and you first order of business, here, has to be addressing this issue first! Failure to do this will make most anything you need to do take ten times longer than it should, and may make some tasks impossible.

How about giving us some specific details on your data, especially the nine fields to be searched.

Linq ;0)>

[orange]

see this example in the code repository -not exactly what you're asking, but may have some ideas you can use.
https://www.accessforums.net/code-re...rch-43055.html

[Access_noob_]

So here is the reason for the odd set-up;
I work for a maintenance company and am trying to simplify the process of locating the correct replacement part. Each item has up to nine fields, our part number, vendor number, model, serial, etc. When an asset is determined to need replaced, the tech may bring any one of these numbers to us depending on what they were able to find on the part. For instance, if they have a manual, they may bring us a catalog number, or if they only have the item itself, they will come to us with maybe a model, serial or some other marking directly on the part. So I was hoping to have one search box that would check all of the possible fields. Also, this specific table is going to be used exclusively for archival purposes and not integrated with any other features. Hope this clarifies a bit. Thanks.

[ssanfu]

OK, Now I understand. You should be able to create the code to search the fields. Just watch the delimiters....good luck.

[Thompyt]

Why don't you use the record search button function using the button macro wizzard? When you select search it will searc in whatever filed your cursor is in. Select the field with cursor, then click the search button.

[June7]

A dynamic parameterized query is one way to handle this type of search. The same criteria parameter must be under each of the 9 fields - each on a different Criteria row to invoke the OR operator - looks like stair steps.

LIKE "*" & [input value here] & "*"

The [input value here] will trigger a parameter input popup. Problem with popup is can't validate user input.

Better would be to reference a combobox on form.

LIKE "*" & [Forms!formname!controlname] & "*"

Combobox RowSource could be the UNION query described by ranman (only without the filter criteria) so users can only type in or select valid data.

[Missinglinq]

...Each item has...our part number, vendor number, model, serial, catalog number, etc

...the tech may bring any one of these numbers to us depending on what they were able to find on the part...

...they may bring us a catalog number, or...a model, serial or some other marking...

Even with this business need, unless this is a very, very small database, you'd be better off having a separate Control for each type of search term; otherwise, searching nine different Fields, with the Like operator (which you have to do, for this kind of thing) is very likely to retrieve many, many non-appropriate results.

Linq ;0)>