Searchable text field for keyword in text

[misty69]

Hi all

I need to be able on a query to search within text in a field for a specific keyword. I've tried the usual like[enter word] but I can't seem to be able to limit the search to find just one word amongst many.

Any ideas anyone?

Thanks in advance

Misty69

 

[dhookom]

Use

Like "*" & [Enter Word] & "*"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misty69]

Hi dhookom

Thanks for your reply.
I've tried that one but it will only find all the text in the field. If i have for example "i teach large groups" in the field, i want to search for "large groups" with the Like "*" & [Enter Word] & "*" criteria i'd have to put in the search "i teach large groups" in order for it to retrieve the data. what i want to put in the search is "large groups" or some other key word/s. Also, do you know if it would make a difference if i used a memo field instead of a text field?

I'm completely stumped, how about you?

Misty69

 

[lespaul]

So you are saying that this query:

SELECT * FROM SomeTable WHERE MemoField LIKE "*large groups*"

doesn't return the record where the memo field contains "I teach large groups"? It should!



Leslie

 

[misty69]

Hi Leslie

Yes it does but only with the word already in the query, what i want to do is to enable the user to search for different words within the field. It might help if I explain the nature of the field that the search is based on. The field contains information on the teaching styles of all the teaching staff. The idea is that other teachers can search for other members of staff's descriptions of their teaching styles to find the right member of staff for support etc. so the words within the fields will change with each teacher.Therefore the search must be adaptable to find all the teachers with the key words 'large groups' or 'teaching abroad' etc. I know it would be a lot easier to use seperate fields for each teaching style; but 'the powers that be' dictate what they want to be able to do. I'm sure it must be possible to do it...er...isn't it??!??

Thanks for you help

Misty69

 

[dhookom]

I think we missed the part where you were supposed to tell us that you wanted to be able to enter multiple keywords at one time to search for.

What you are attempting to do is quite complex considering your current structure. I would set up a related table of keywords. This would be much easier to search.

If you can't change your structure, you may need to write some code that modifies the SQL of a saved query. Users could enter multiple keywords separated by commas in a form control. Code could loop through the keywords and build a SQL statement using multiple " Or "s in the criteria.

If you don't know how to write or maintain code, go back to the keyword table or maybe someone will create the code for you. It would really help to know the table and field names if you want someone to provide the code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misty69]

Thanks Duane,

I'll try modifying the query with 'or's it sounds plausible.

Misty69

 

[lespaul]

You may want to let the "powers that be" know that by continuing with a non-normalized database model, you will find that your queries are going to get harder and harder to get the correct information.

Read The Fundamentals of Relational Database Design.

you should take Duane's advice and set up a list of regularly used terms (allow the users to add new terms in the UI) and then have a junction table:

Teachers
ID
Name
etc

Teaching Styles
ID
StyleDescription

Teacher Styles
TeacherID
StyleID

with this setup the teachers can have multiple teaching styles, the data is normalized and you can present it in such a way that the users don't recognize that it's stored separately.

HTH

Leslie


Leslie

 

[misty69]

Thanks everyone

The database is normalised but i don't know why it didn't work in the first place. It now works with
Like "*" & [Enter Word] & "*" , guess I was havin a blond moment there!!

Thanks

Misty69