Query using Like Operator

  • Thread starter Thread starter RKH
  • Start date Start date
R

RKH

I have created a search form using the following query to fill the subform.
It works as long as I type in the exact data in the column. I would like to
use the Like operator but when I do I get no results.

This is the original query:

SELECT Church.ID, Church.Name, Church.Address1, Church.Address2,
Church.City, Church.State, Church.PostalCode, Church.Phone, Church.Fax,
Church.Email, Church.Website, Church.Notes, Church.Pastor, Church.Extension,
Church.Ethnic
FROM Church
WHERE (((Church.Name)=[Forms]![SearchChurch]![Name]))
ORDER BY Church.Name;

Changed the query with the like operator as follows but give no results.

SELECT Church.ID, Church.Name, Church.Address1, Church.Address2,
Church.City, Church.State, Church.PostalCode, Church.Phone, Church.Fax,
Church.Email, Church.Website, Church.Notes, Church.Pastor, Church.Extension,
Church.Ethnic
FROM Church
WHERE (((Church.Name) Like ([Forms]![SearchChurch]![Name])))
ORDER BY Church.Name;

Richard
 
It would all work better for you if you used a combobox to select the church
name. Create a query that gets an unique list of all the church names from
your database and sort the names ascending. Set Limit To List for the
combobox to Yes and set Autoexpand for the combobox to Yes. Use for your
criteria:
WHERE (((Church.Name)=[Forms]![SearchChurch]![NameOf The Combobox]))

You'll be able to start typing in the church name i9n the combobox, the list
will automatically scroll to names beginning with the characters you typed
and when you see the name you want, click on it.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
You need to add the wildcard character (*) if you want to be able to enter
just the initial letters of a church name:
SELECT Church.ID, Church.Name, Church.Address1, Church.Address2,
Church.City, Church.State, Church.PostalCode, Church.Phone, Church.Fax,
Church.Email, Church.Website, Church.Notes, Church.Pastor, Church.Extension,
Church.Ethnic
FROM Church
WHERE (((Church.Name) Like [Forms]![SearchChurch]![Name] & "*"))
ORDER BY Church.Name;

Note that Like will not let you "phonetically" spell a name and try to match
on that. If you want to select a name from the actual list of church names
that are in the database, then Steve's suggestion of using a combo box is
the right approach.
 
Back
Top