Search box in Form using keyword

[Guest]

I want to create a search text box on a form that searches an entire table or
particular columns of a table for the user-entered keyword, and then displays
the results on the same form.

I really appreciate anyones help on this.

 

[Jeff Boyce]

If I understand your post correctly, you are describing a table that could
have a "searched-for" string located in any of several different columns.
If true, this sounds very much like ... a spreadsheet!

In a well-normalized relational database design, you'd be more likely to be
searching for a text string in a single column.

Could you describe your data structure a bit more? It would help in
offering suggestions to finding a sought-string.

 

[Guest]

Hi Jeff, thanks for your help.

It is a lot like a spreadsheet. Infact, it was imported into Access from
Excel.
There is a table called Items. Each row represents an item, with an ID,
Date..., Description, and physical location.

Someone who is looking for a particular item and its location should be able
to type in a few keywords (for instance "security bulletin"), and the search
would bring up any row from the Items table in which the description
containers those keywords.
Sometimes someone may want to search by date along with a keyword for
instance.

I would like the search box and the results to appear on a single form, and
user-friendly. Perhaps its better to have several search boxes, one for each
column; then just have if Column1n=true AND Column2n=true then add ROWn to
results.

I appreciate any detailed help or examples you may have.

Thank you,
Katayon

 

[Jeff Boyce]

If you check the tablesdbdesign newsgroup, you'll find many suggestions
about "normalizing" data. As long as you are only using Access as a way to
hold (un-normalized) Excel data, you may as well keep working with Excel.

What business need are you trying to solve by using Access? Perhaps you
don't actually need to move from Excel...

If you are convinced that the strengths of a relational database (Access!)
can help you do something you cannot in Excel, the first thing you need to
do is revisit your data model. Do this with paper and pencil, and with the
computer turned off. When you have a good "entity-relationship" model, post
to the tabledbdesign newsgroup and see what the readers think. They can
also help you get started.

Do check out "normalization" before proceeding...

 

[Guest]

I am trying to recreate a database that existed at another company working on
the same contract.

an approxtimate 100 people can not be given access to an excel spreedsheet
for searching and modifying. We need the database for serveral reasons.

I am just trying to figure out the path for creating a form that searches
for user-defined keywords and criteria and then displays the results.

I've searched in the other forms, and have found no similar questions.

It would be greatly appreciated if someone can please guide me on this issue.

thank you

 

[Jeff Boyce]

My responses have been intended to cause you to (re-)consider the data
structure/design of your data base. If you keep what I understand is your
current design, with the data you search for being in any number of
different fields, you will not benefit much from the strengths Access
offers.

My recommendation is that you normalize your data before proceeding.

 

[George Nicholson]

Perhaps its better to have several search boxes, one for each column;

Yes, then use code to build an SQL string for a Select query from the
criteria supplied, so you'd end up with something like:
"SELECT * FROM Items WHERE MyDate = #" & MyForm.txtDateCriteria & "# AND
Description Like '*" & MyForm.txtDescriptionCriteria & "*'"

Then change the Recordsource of your "display" form/subform to that SQL
string. If the user clears all criteria then the recordsource reverts back
to "SELECT * FROM Items".

If you need to apply a criteria to more than one field, something like:
"WHERE MyDescription = '" & MyForm.txtDescrCriteria & "' OR MySubDescription
= '" & MyForm.txtDescrCriteria & "'"

All this can be *very* tricky. It may be easy to build an SQL string if the
user supplies criteria for all allowable (3?) fields, but you also need to
have your code build a valid SQL string if the user wants to filter on fewer
than "all" fields. Other things you might have to address: allow date ranges
(i.e., BETWEEN)?, allow greater than or less than date criterias?, allow the
user to save criteria with a name for later recall? allow user to save
results, or just view them?.

Suggestion: start with one or two fields and keep it simple (while keeping
flexibility for future growth in mind). Add more functionality once you have
a "basic" search up & running. You may find that requests for additional
bells & whistles will be a never ending battle, so focus on getting it right
in acheivable phases.

Note: Date is a reserved keyword. It is a *very, very, very* good idea to
name the field something other than that. You can use it as a label or
caption, but the actual field name should be something different.

HTH,

 

[Guest]

Thank you!

Yes, then use code to build an SQL string for a Select query from the
criteria supplied, so you'd end up with something like:
"SELECT * FROM Items WHERE MyDate = #" & MyForm.txtDateCriteria & "# AND
Description Like '*" & MyForm.txtDescriptionCriteria & "*'"

Then change the Recordsource of your "display" form/subform to that SQL
string. If the user clears all criteria then the recordsource reverts back
to "SELECT * FROM Items".

If you need to apply a criteria to more than one field, something like:
"WHERE MyDescription = '" & MyForm.txtDescrCriteria & "' OR MySubDescription
= '" & MyForm.txtDescrCriteria & "'"

All this can be *very* tricky. It may be easy to build an SQL string if the
user supplies criteria for all allowable (3?) fields, but you also need to
have your code build a valid SQL string if the user wants to filter on fewer
than "all" fields. Other things you might have to address: allow date ranges
(i.e., BETWEEN)?, allow greater than or less than date criterias?, allow the
user to save criteria with a name for later recall? allow user to save
results, or just view them?.

Suggestion: start with one or two fields and keep it simple (while keeping
flexibility for future growth in mind). Add more functionality once you have
a "basic" search up & running. You may find that requests for additional
bells & whistles will be a never ending battle, so focus on getting it right
in acheivable phases.

Note: Date is a reserved keyword. It is a *very, very, very* good idea to
name the field something other than that. You can use it as a label or
caption, but the actual field name should be something different.

HTH,