Search a field for a keyword(s)

[Mavig]

I have a field with a sentence worth of data. For example. "What is happening
in the Financial Market with respect to the Sub-prime mortgages issues." If I
wanted to find all fields that have the word or phase "Sub-Prime mortgages".
How can I do this. I want to be able to type in the keyword and search for it
with-in the field.

 

[Jeff Boyce]

You don't mention where you are trying to do this.

My recommendation would be via a form. Use the form to collect what you (or
your application's users) are looking for, and use a query to do the
searching. In your query, you would use a wildcard to help. In the
selection criterion, put something like:

Like * & Forms!YourCollectionForm!YourCollectionControlName & *

Now add a command button to the form that runs the query.

When you click the command button on the form, the query tries to run, using
the value in the Control. If there's nothing in the Control, you'll get
every record. If there's something in the Control, you'll get those records
that have the something somewhere in the field. If you don't open the form,
the query fails.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mavig]

yes in a form, I'll try it. - thanks

You don't mention where you are trying to do this.

My recommendation would be via a form. Use the form to collect what you (or
your application's users) are looking for, and use a query to do the
searching. In your query, you would use a wildcard to help. In the
selection criterion, put something like:

Like * & Forms!YourCollectionForm!YourCollectionControlName & *

Now add a command button to the form that runs the query.

When you click the command button on the form, the query tries to run, using
the value in the Control. If there's nothing in the Control, you'll get
every record. If there's something in the Control, you'll get those records
that have the something somewhere in the field. If you don't open the form,
the query fails.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mavig]

Hi Jeff, thank you very much. It works great.

You don't mention where you are trying to do this.

My recommendation would be via a form. Use the form to collect what you (or
your application's users) are looking for, and use a query to do the
searching. In your query, you would use a wildcard to help. In the
selection criterion, put something like:

Like * & Forms!YourCollectionForm!YourCollectionControlName & *

Now add a command button to the form that runs the query.

When you click the command button on the form, the query tries to run, using
the value in the Control. If there's nothing in the Control, you'll get
every record. If there's something in the Control, you'll get those records
that have the something somewhere in the field. If you don't open the form,
the query fails.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

de nada

Jeff

Hi Jeff, thank you very much. It works great.

 

[Mavig]

Hi Jeff,

I need to take it to the next level. After it finds the records with the
matching key word. I want to run a report to show the field(s) that contain
the keyword.
for example: record one it finds "Finance" in "fields 2" and " field 3"
so I only want to show "fields 2" and "fields 3" not "field 1", "field 4"
and "field 5"
Right now is gives me the whole record.

Any help would be greatly appreicated.

 

[Jeff Boyce]

Mavig

A suggestion ... if you post a new question/issue as a new post, rather than
as a continuation, you'll get more folks looking at it and considering
ideas.

"How" depends on your data ... it all starts with your data.

If you have records that can have "Finance" in field2 or field3 (or field1
or 4 or 5), you have a spreadsheet, not a relational database.

This is an important distinction because Access expects
relationally-oriented data to get the best use of its relationally-oriented
features and functions.

Please post back a description of how your data is structured.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mavig]

Yes it is flat. I have a simple table. with the following fields:
Team,Question, and 5 fields(answers) for each question. I can have up 25
Teams and several questions.
I have a separate table for the questions that are linked by Q# to the Team
tables. Each Team has their own table for their data to be stored. Then I
merge all the data into one master table and run the queries.

Thanks

 

[Jeff Boyce]

Mavig

"each Team has their own table" and "5 fields (answers) for each question"
describe, as you've said, a "flat" table structure more common for
spreadsheets.

You (and Access) will struggle to get Access to do the things you're trying
to do by feeding it 'sheet data.

My first suggestion is to turn off Access and revisit your data structure.
If "normalization" and "relational" are not familiar terms, plan to spend
some time brushing up on them before expecting Access to do something it
wasn't designed to (easily) do.

If you are working with questions, you might want to take a look at Duane
H's AtYourSurvey sample database for ideas on a well-normalized table
structure:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP