You are here:

Using MS Access/Using LIKE function with user input


QUESTION: I have a database with tables of names and experiences.  I want to be able to create a report that lists everyone with a common experience, and have a query which uses the LIKE function to bring up the correct list of names.  In the query, in the "Experience" entry, I have set the criteria as LIKE [Search Criteria].  When a user opens the query he is asked to provide the search criteria -- searching for everyone who owns a Chevrolet the user could input *Chev* or *Chevrolet*, which would bring up every entry that includes the word "Chevrolet".

Because the database will be used by the public I want to make it more user-friendly and not require that users include the wild card character "*" in their input.  In other words, the user would enter "Chevrolet" and the query would return every record that included those letters -- as though the entry had used wild card characters, *Chevrolet*.  This allows the database to include: Owns a Chevrolet; Drives a Chevrolet; Chevrolet-owner; etc.

How can I modify the selection criteria to accomplish this?

ANSWER: Simple. Make the criteria:

LIKE "*" & [Search Criteria] & "*"

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I'm trying to set up an Access database for a historically significant alumni organization which is very small in size.  When I'm finished I want to be able to accomplish several things:

1.  Be able to show how the entire list of alumni are linked to a variety of historical figures, jobs or events; (e.g., a report in table format that shows Alumnus Joe Brown is linked to President Kennedy by means of having served as an intern on his staff; Alumnus Sam Smith is linked to the USN Blue Angels as the solo exhibition pilot between Year X and Year Y; Alumnus Bill Jones is linked to a job position of CEO as the CEO of XYZ Corporation, from Year X to Year Y.)  I have got this set up.

2.  Do the reverse -- Be able to show how the entire list of historical figures, and the entire list of activities, relates to the alumni, name-by-name. (e.g., Vietnam War: Sam Smith was at Cam Ranh Bay, Bill Jones was on-board the USS Constitution, Joe Brown was in the Pentagon planning department.)  This also has been done.

3.  Find a user-friendly way to ask for info concerning a specific alumnus, and his relationship to historical figures or events.

Possible approach: A report page which lists all alumni by name; clicking on a specific name would take you to a new report that shows that alumnus's relationships to every apropriate historical figure or event.

4.  A similar user-friendly way to select a single event and automatically create a relevant report that shows all of the alumni that are linked to that single event.

I'm close with item #3, because I can create a report similar to the all-grads list, but with a criteria of a single grad, using either last name or an assigned ID#.

No. 4 is harder, because I want the entire list of events or historical figures to be known and allow the user to select one of them to see which alumni are involved.  The "LIKE" command may create more than one answer.  Therefore, it seems to me a list of events (a form or a query listing every event), with the ability to select one event and go to the relevant report for that event, likely is the best solution but I can't figure out how to do it.

First, for future reference, please start a new question rather than piggyback on an old one. All-Experts limits the amount of follow-up so you may have to start a new thread anyway.

Its hard to answer these without knowing the structure of your database. If I were setting up such a database I would do it like this:

tblAlumni: Info about each Alumnus, AlumniID, names, address, Grad year, degree, other info specifc to the alumnus.

tblConnection: ConnectionID, Connection Name, type of connection (either person, event, job, etc.),

tblAlumniConnection: Info about the connection: AlumniID, ConnectionID, Description (how the person is connected), other info about the connection.

Given a structure like this, I would create a form in Continuous form mode bound to tblAlumniConnection. On that form I would have comboboxes for AlumniID and ConnectionID to use to display that info. I would then use this form as a subform on 2 different main forms. The first mainform would be bound to tblAlumni and it would show details about the Alumnus. On this form you would use the Combobox wizard to create a search combo (preferably in the Header) You would use the third option in the combobox wizard to do this. A search combo, allows you to select a value (in this case Alumnus name) and bring up the matching record. The subform, linked on AlumniID, would then display all connections for that alumnus.

The second form would be bound to tblConnection. Again you would have a search combo to select a connection. This would bring up that connection to show the details and the subform, linked on ConnectionID, would display all Alumnus with that connection.

You would design reports to to mimic the forms. One report grouped on Alumni, the other on Connection. These reports would be bound to a query that links the three tables to show all the details. Finally, you would have a print preview button on your forms to call the appropriate report. You would use the WHERE clause of the OpenReport method to specify what to report on. For example, if you are calling the Alumni report, the OpenReport method would look like:

DoCmd.OpenReport "reportname",,,"[AlumniID] = " & Me.AlumniID

Me.AlumniID refers to the control on the form that is bound to the AlumniID. Similarly on the Connection main form the WHERE clause would be:

"[ConnectionID] = " & Me.ConnectionID.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.