Okay, here we go. First, a little explanation. In your Form Options, under Programming, you need to set your language to C#. Next, you'll need to change your query button. Currently, if you look in the button properties, you'll see it's set to run a query. Change that to Rules and Custom Code. Select the Edit Form Code button. VSTA (Visual Studio Tools for Applications) opens up. You'll see an event handler for your button with a helpful "Write your code here" comment.
Here is a code sample:
//get the value of the query field we want to use with a wild card
XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Customers/@Last_Name", NamespaceManager);
string querySeed = queryField.Value;
//clear the query field so that it is ignored in the query
queryField.SetValue("");
//create our custom where clause -- you have some choices here. I've added in the wildcard. You could choose not to, if your users are savvy and can fill it in themselves when searching, all up to you.
string customQuery = " where ([Last Name] like '" + querySeed + "%')";
//get the original command (we'll need to set it back)
AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;
string origCommand = newQuery.Command.ToString();
//set the query to use the custom command
newQuery.Command = origCommand + customQuery;
//annnnnnddddd, now we run the query using our shiny new command
newQuery.Execute();
//put everything back the way it was.... you're going to need it!
newQuery.Command = origCommand;
//set the query field back for the user, so they know what they typed in!
queryField.SetValue(querySeed);
I've tried to liberally comment the code, hopefully that helps sort it out. If you want to try it as is, you can create a new form off the Northwind sample db for Access -- I used the Customers table, and used "Last Name" as they query field. You can, of course, expand this -- you can even mix these wild cards with filled out fields! So, for example, if you had some drop downs like Department or Area or whatever, your user could select those too. Here is a link to the Microsoft sample I used to figure this out -- mind you, the sample is in jscript, but they aren't so far apart, really.