Searching for part of a field

[cheyenne2002]

Is there a way to do a query that will pull out records if just one word is in the field.

I have a field that contains the names of the committees a person is part of (Education, Membership). I want to pull out only those that are part of the Education, but the field contains both words.

Is there a way to pull records that have Education along with other possible committees?

Thank you.

 

[fneily]

Look at the Instr() function in Help.

Why do you have a field that contains multiple data? You might want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[genomon]

What he said. Redesign the DB. First normal (1NF) is your friend. (you'll find a definition in the book)

"Teamwork means never having to take all the blame yourself."

 

[cheyenne2002]

Unfortunately I did not create the database someone else who does not know about relational databases did and they created a field to list the committes people joined.

I would have done it totally different. Unfortunately, at this point I am stuck with it as it is until the company decides to put some money into it and have it rebuilt by a professional.

Is there anything I can do at this point?

 

[genomon]

Yes. Read fneily's reply.
"Look at the Instr() function in Help."

"Teamwork means never having to take all the blame yourself."

 

[ProgramError]

or use

like "*" & "yourword" & "*"

in your criteria. "yourword" could also be a prompted input, combobox or textbox.

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[genomon]

Yes, but LIKE will return true for fields with Education, Membership as well as just Education. I would look for a comma, and if it's there, assume (and that is dangerous to do) the field contains two words. Such as InStr(1, fldData, ",").

"Teamwork means never having to take all the blame yourself."

 

[Golom]

If they are comma separated then

"," & Replace([FieldName]," ","") & "," LIKE "*," & Replace([SearchFor]," ","") & ",*"