Using Like

[CETInnovator]

Hello,

I am working on a query and having some difficulty pulling back the information that I want. I would like to my query to prompt a user to enter in a customer name and have access return the contact information for the customer. The issue is that I can't figure out if I should use "Criteria" so that the prompt can act as a fuzzy search engine or should I be using VBA code. The criteria that I have is below:
Like '*'+[Enter Customer Name]+"*" Or Like '*[&]*' Or Like '*[-]*'

when I get my results, it pulls back the customer name that I've entered in the prompt and all other names that have an & sign or - in the name. I only want to pull up the name I entered and if the customer name has a special character, I would like for the query to recognize it.

Can anybody help me?

 

[jdraw]

Will the user be able to identify a Customer from a list of Customers? If so, then I suggest you use a form with a combo box of Customer names. When the user selects a Customer from the combobox, the run a query to select that Customer's details.

there are some free video tutorials here

http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[CETInnovator]

There will not be a list for a user to choose from. This is a list that a user could use if they wanted to know who the resources were for a customer? So, the output would be the support resource with their contact information.

 

[CETInnovator]

Unfortunately, that didn't work. It worked if I included the "&" in my search but if I put in a name that doesn't have "&" or "-" then it doesn't bring back a name.

 

[jdraw]

& is the Access concatenation symbol. It has special meaning in Access.
What special characters are you looking for? Do you have a few samples?

 

[CETInnovator]

Sure. An example using the "&" sign would be like searching for A&B Corporation. I want to be able to type in the name with or without the "&" sign and still pull back the information.

An example of "-" would be Pick-way or pickway

 

[jdraw]

I've worked with databases where we had a number of names with special characters in their names. As a rule we copied all alpha and numerics to a Searchname field in the CompanyRecords. We basically dropped the special characters from search. So you searched for company Name you actually searched the Searchname field and it was without the special characters.

Tell us more about the Table structure involved.

 

[jdraw]

I just did a test with one of my tables

This query

SELECT NewHires.Lastname
FROM NewHires
WHERE (((NewHires.Lastname) Like "*" & [EnterSpecialCharacter] & "*"));

will Prompt for EnterSpecialCharacter
and if you enter & it will retrieve Jac&T from the newHires Table.
NewHires Table
Lastname

Smith
Jones
Sajban
Thomson
Jac_zin
Jac&T
Baw*yy

 

[CETInnovator]

It's a simple table. It has the name of the company and the support persons contact information. The goal of the database is to be able to search a company's name and find out who supports the company.

My query allows me to pull up the information for an individual company, but it also pulls in any company that has "&" or "-" in their name.
"If" Like '*'+[Enter Customer Name]+"*" Or Like '*[&]*' Or Like '*[-]*'

I only want the information for the customer name that I typed in.

 

[CETInnovator]

Thanks so much for your help. Not sure what I'm doing wrong but I am getting a syntax error. Will keep trying