Searching for Specific Text

[Ahmed@soundevo]

Hi all,

I'm back again. Thank you for the help with my last question however I have another question now. I have created another database that stores customer enquiries for example the date it came in, the member of staff that handled it, the vehicle details, customers name/number, nature of enquiry, solution, etc..

I have created a query that allows me to search for enquiries by member of staff as well as vehicle make and vehicle model. In the customer details section it is required to enter the customers name and then the number/e-mail address for example:

"Chris - 02081234567" or "Chris - Chris@email.com" or "Chris - 02081234567 - Chris@email.com"

The query that I want to create, for example if you want to search for the customer name, it would like it to be similar in the vehicle make query in which when you double click on that query it asks for you to type in the vehicle make. So if you type in "BMW" it will search for all of the records that contain "BMW".

How can I do it so that the query asks for customer name and then it'll search the relevant field and if it contains the customer name then it will show it. I made one query that asked for the customer name however it didn't show it as it had the contact number in it too (or at least this is what I assume).

Can it be done so that within that field of text it will search it and if the field contains the required string it will then display it as a result of the query ? Taking my examples before:

"Chris - 02081234567" or "Chris - Chris@email.com" or "Chris - 02081234567 - Chris@email.com"

If you type in "Chris" in the search box when it asks you to then it will show all of the "Chris'" in the database.

Sorry in advance for this post being so long winded. Thank you for taking the time to read it and help out.

If required I can upload a copy of the database if it makes things easier.

 

[pr2-eugin]

Not sure if this is what you want.. but try this out..

SELECT Customers.[CUSTOMER ID], Customers.[FIRST NAME], Customers.[LAST NAME], Customers.TEL
FROM Customers
WHERE Customers.[FIRST NAME] Like "*" & [Forms]![Search Form]![FirstName] & "*"

in the above code [FirstName] would be the text field.

 

[Ahmed@soundevo]

Not sure if this is what you want.. but try this out..

SELECT Customers.[CUSTOMER ID], Customers.[FIRST NAME], Customers.[LAST NAME], Customers.TEL
FROM Customers
WHERE Customers.[FIRST NAME] Like "*" & [Forms]![Search Form]![FirstName] & "*"

in the above code [FirstName] would be the text field.

Would this allow me to search for a specific customer name ? Say for example if I go to create a new query in design view, enter the relevant fields and put this in the customer name/number/email criteria section ? All I really want to search for is customer's first name

I tried that out and it said the syntax was incorrect

 

[pr2-eugin]

If you want to look out for only the first name yes.. Go on to Create a Query, and type in the above query (for your table and column names obviously) the 'like' part should come as the criteria..

 

[Ahmed@soundevo]

If you want to look out for only the first name yes.. Go on to Create a Query, and type in the above query (for your table and column names obviously) the 'like' part should come as the criteria..

I'm confused. Can you explain more ?

Sorry

 

[pr2-eugin]

Okay..
1. Create a Query using the 'Query Wizard', select the table from which you need to search, input all the requiered fields. For example, let us consider the table name is Customers and you want the CustomerID, FirstName, LastName and Telephone fields(Columns)
2. Instead of opening the Query results, select the Modify the Query option and click finish.
3. In the design view you will have the columns placed with the criteria field empty, under the FirstName column's criteria type in Like "*" & [Forms]![Search Form]![FirstName] & "*" (THIS IS BECAUSE YOU HAVE CALLED THIS QUERY FROM A FORM THAT RECIEVED AN INPUT for searching)

**you can also use the Query Parameter. if you want to, just replace [Forms]![Search Form]![FirstName] with [EnterName:]..

Save the query and execute it. You will get the result. Hope this is clear.

 

[Ahmed@soundevo]

Okay..
1. Create a Query using the 'Query Wizard', select the table from which you need to search, input all the requiered fields. For example, let us consider the table name is Customers and you want the CustomerID, FirstName, LastName and Telephone fields(Columns)
2. Instead of opening the Query results, select the Modify the Query option and click finish.
3. In the design view you will have the columns placed with the criteria field empty, under the FirstName column's criteria type in Like "*" & [Forms]![Search Form]![FirstName] & "*" (THIS IS BECAUSE YOU HAVE CALLED THIS QUERY FROM A FORM THAT RECIEVED AN INPUT for searching)

**you can also use the Query Parameter. if you want to, just replace [Forms]![Search Form]![FirstName] with [EnterName:]..

Save the query and execute it. You will get the result. Hope this is clear.

Hi,

Thanks for that however it Didn't work. It also asked for the search thing twice so like enter customer name and then it'd ask again. Do you want me to send the database ?

 

[pr2-eugin]

No.. Just give your query.. and also the Table design.

 

[Ahmed@soundevo]

No.. Just give your query.. and also the Table design.

Click on the link to download it. https://rapidshare.com/files/1756604047/Enquiry_Database.accdb

The query is called "Customer Search Query"

 

[pr2-eugin]

The query is called "Customer Search Query"

No such query present inside the file you had uploaded.

 

[Ahmed@soundevo]

No such query present inside the file you had uploaded.

Try this one

https://rapidshare.com/files/776005554/Enquiry_Database_Test.accdb

 

[Ahmed@soundevo]

Any progress or any further help ?

 

[Beetle]

First, you've got some table design issues. You should not store Customer Name/Number/E-Mail all in the same field. These are separate attributes and as such should be stored in separate fields. Ditto for your Quotation/Solution field and you really should have separate tables for Staff and Vehicle info, but I digress I suppose. The point being you really should further normalize this before you get too far down the development road, as it will only become more difficult to correct as time goes by and more data/objects are added.

Having said that, you have a couple of problems here;

1) Your Customer Search query is based on the Enquiries by Staff Name query, which itself has a parameter prompt in the Staff Name column. Combine this with the parameter prompt you're using in the Customer Search query and that is why you're getting prompted twice. You need to base the Customer Search query on the table, not the other Staff Name query.

2) The criteria you're using in the Customer Search query is;

"*" & [Forms]![Search Form]![FirstName] & "*"

There are several problems with this;

a) You need to precede this with the Like keyword.
b) You don't have a form named Search Form.
c) You don't have a field named FirstName.

(those were just example object names from the previous post)

The only form you have in this db is Enquiry Form, but that's a data entry form based on the table, not a search form.

So, you have a couple of options (once you correct the source of the query from #1). If you want to open the query directly and get a prompt for a Customer Name to search for, then you need to put criteria in the Customer Name/Number/E-Mail column that looks like;

Like "*" & [Enter A Customer Name] & "*"

If you want to have a form for the user to enter the name to search for, then you need to create one, then reference that form name in your criteria, like;

Like "*" [Forms]![Your Form Name]![Your Text Box] & "*"

in the above, [Your Form Name] should be replaced with the actual name of your form, and [Your Text Box] should be replaced with the actual name of the text box on that form where the user enters the name to search for.

 

[vbaInet]

Adding to what Beetle said, if you want the search performed on all the fields within the table you MUST include that LIKE clause under the Criteria row of ALL those fields.

 

[Guus2005]

... or if you want to search for a string in *ALL* the fields, paste them together and use the Instr function in the where clause.

From the above example:

SELECT Customers.[CUSTOMER ID], Customers.[FIRST NAME], Customers.[LAST NAME], Customers.TEL
FROM Customers
WHERE INSTR(1,Customers.[CUSTOMER ID] & Customers.[FIRST NAME] & Customers.[LAST NAME] & Customers.TEL
,[Forms]![Search Form]![FirstName]) > 0

Give or take a few ampersands (&) and double quotes(").

Share & Enjoy!