where clause with contains statrment

[davedinger]

im working on an enployee directory where im having issues searching the Hobbies field for a requested hobbie.

My Table named EmployeeInformation contains a short text field named Hobbies among others.
my isssue is with the hobbie field.

So, people are entering tings like "fishing, Biking, Boating" in to the hobie field
Basically they are entering multple words in this field. I am trying to figure out how to open a form where they can enter a word into a text box and find any record that has that word anyplace in the hobbies field,

So this is what I have
I have s form named Employee Directory that im using as a way to filter another form named Employee information list so that it only shows desired records.

The Employee Directory form has a Text Field currently named Text1, and several command buttons on it like Hobbies that when clicked should open the Employee Information List form based on what is in the Text1.



My Problem
If i enter the word Fishing in the field Text1, and click on the hobby button it wont bring anying because Fishing may only be one of several words in a records Hobbies Field.

Im assuming this is an easy "contains" statement of some type but im not getting it.

Can you Help?

Thanks

Dave

[davedinger]

sorry i just realizes sometimes used the word hobby and some time i used the word hobbies, all fields and buttons should be hobbies

[Edgar]

Your where clause should look like this: ... WHERE hobbies LIKE '*" & Text1 & "*';

How does it look?

[ArviLaanemets]

Practically you have a poorly designed Excel table instead of database table!

Main problems with such design:
You have to type into field all hobby names for every employee - which takes a lot of time compared with simply selecting them from list of registered hobbies (e.g. using combo box);
There will be no control over what you type in - so your DB will be prone to typing errors (as follows, you can never be sure, the result you get from any query will be correct);
In case you discover a typo in hobby name, to correct this you have to search the whole table for this particular typo, and correct it everywhere where it was found;
Getting any info organized differently as in your current hobbies field will be at least very difficult (the theme of your current post!)

Normal design will be:
tblEmployees: EmployeeID, Forename, LastName, ...;
tblHobbies: HobbyID, HobbyName;
tblEmployeeHobbies: EmployeeHobbyID, EmployeeID, HobbyID.

When there is a need to correct the name of some hobby, you do this in tblHobbies. The change will apply immediately everywhere in your DB!

In your database/FrontEnd you either have a single form based on table tblEmployees, or an unbound form with combo to select EmployeeID from tblEmployees (with employees full name composed from ForeName and LastName, and displayed in combo);
In this form, you create a subform, which has continous form based on tblEmployeeHobbies as source. The main and subform are linked through EmployeeID in case main form being bound one, or through combo in main form, and EmployeeID in subform in case the main form being unbound one. The control for EmployeeID in subform will be hidden, as it will be filled automatically with active ID value in main form whenever a new record is created. To add a hobby into subform, you'll use a combo with query from tblHobbies as RowSource linked to EmployeeHobbyID of tblEmployeeHobbies, where only hobby name is visible. As you can limit the allowed entries to ones listed in combo's RowSource query (i.e. being entered into tblHobbies), users can't enter any hobbies not registered previously.

With such design, you can easily query:
All hobbies of certain employee (like Select * FROM tblEmployeeHobbies WHERE EmployeeID = SomeEmployeeIDValue);
All hobbies of any group of employees (like Select DISTINCT * FROM tblEmployeeHobbies WHERE EmployeeID IN (EmployeeIDValueList);
Info about employees having certain hobby (like Select e.* FROM tblEmployees e INNER Join tblEmployeeHobbies eh ON eh.EmployeeID = e.EmployeeID WHERE eh.HobbyID = SomeHobbyIDValue);
The List of all employees having at least one hobby from given list of hobbies (like Select DISTINT e.* FROM tblEmployees e INNER Join tblEmployeeHobbies eh ON eh.EmployeeID = e.EmployeeID WHERE eh.HobbyID IN (HobbyIDValueList);
Etc.

[davedinger]

DoCmd.OpenForm("Employee Information List", acNormal,,hobbies LIKE '*" & Text1 & "*')

[Micron]

DoCmd.OpenForm("Employee Information List", acNormal,,hobbies LIKE '*" & Text1 & "*')

That will raise a syntax error. Assuming 'hobbies' is the correct reference, use no parentheses and add the missing ending double quote

DoCmd.OpenForm "Employee Information List", acNormal, , "hobbies LIKE '*" & Text1 & "*'"

[davedinger]

Awesome, it worked great. I knew it had to be something like that.
Thank you for all of your help!