|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 26th, 2004, 04:39 PM
|
|||
|
|||
Creating a wildcard search for a form.
Hello,
I have a form called "Guest Search" where the user is asked to enter a guest name, which can appear in three different fields in a table. It is also possible that there are multiple entries for the guests in the field e.g Peter, James, John. What I need to do is to have the user enter their search in the guest search form and for the query to find all the entries which are a close match whether they are slightly mis-spelt or appear further down the field e.g James. I currently have the following SQL query, which I have put together from a query which is working, but only on exact matches and then adding suggestions from other posts. However the query doesn't work as it just displays all the results: SELECT SearchTable02.Guest, SearchTable02.[Artist Name], SearchTable02.[Pre-Mix DJ], SearchTable02.[Audio Details], * FROM SearchTable02 WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search] & '*')); Any help with this would be great. 
|
|
#2
May 26th, 2004, 05:33 PM
|
|||
|
|||
|
Creating a wildcard search for a form.
Are you referencing the field correctly?
[Forms]![Guest Search] = a form called Guest Search. .. . . it is not telling it which control on that form to use therefore I think your query is saying select all records where blah blah like * or blah blah like * i.e. not actually filtering anything Trying adding the control name eg WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search]![txtName] & '*')); HTH "Peter Knight" wrote in message om... Hello, I have a form called "Guest Search" where the user is asked to enter a guest name, which can appear in three different fields in a table. It is also possible that there are multiple entries for the guests in the field e.g Peter, James, John. What I need to do is to have the user enter their search in the guest search form and for the query to find all the entries which are a close match whether they are slightly mis-spelt or appear further down the field e.g James. I currently have the following SQL query, which I have put together from a query which is working, but only on exact matches and then adding suggestions from other posts. However the query doesn't work as it just displays all the results: SELECT SearchTable02.Guest, SearchTable02.[Artist Name], SearchTable02.[Pre-Mix DJ], SearchTable02.[Audio Details], * FROM SearchTable02 WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search] & '*')); Any help with this would be great.
|
|
#3
May 27th, 2004, 11:32 AM
|
|||
|
|||
|
Creating a wildcard search for a form - Searching for information in a field divided by commas?
Thank you very much for the help. I now have it that if a search is
carried out a * is entered in the search the results come up. What I am now after is a way of searching so that if there are the three guests listed in the field e.g Peter, James, John it is possible to put a search in the form for "John" and it would retrieve all the entries where John appears whether first, second, or third without having to bring up all the records. Many thanks Peter "Newbie" wrote in message ... Are you referencing the field correctly? [Forms]![Guest Search] = a form called Guest Search. .. . . it is not telling it which control on that form to use therefore I think your query is saying select all records where blah blah like * or blah blah like * i.e. not actually filtering anything Trying adding the control name eg WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search]![txtName] & '*')); HTH "Peter Knight" wrote in message om... Hello, I have a form called "Guest Search" where the user is asked to enter a guest name, which can appear in three different fields in a table. It is also possible that there are multiple entries for the guests in the field e.g Peter, James, John. What I need to do is to have the user enter their search in the guest search form and for the query to find all the entries which are a close match whether they are slightly mis-spelt or appear further down the field e.g James. I currently have the following SQL query, which I have put together from a query which is working, but only on exact matches and then adding suggestions from other posts. However the query doesn't work as it just displays all the results: SELECT SearchTable02.Guest, SearchTable02.[Artist Name], SearchTable02.[Pre-Mix DJ], SearchTable02.[Audio Details], * FROM SearchTable02 WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search] & '*')); Any help with this would be great.
|
|
#4
May 27th, 2004, 06:37 PM
|
|||
|
|||
|
Creating a wildcard search for a form - Searching for informationin a field divided by commas?
Try adding another asterisk after the like in your where lines, such as:
(((SearchTable02.[Audio Details]) Like '*' & [Forms]![Guest Search] & '*')); unless i don't understand this fully... in which case sorry... first attempt at answering a question... cheers, Ben Moore Peter Knight wrote: Thank you very much for the help. I now have it that if a search is carried out a * is entered in the search the results come up. What I am now after is a way of searching so that if there are the three guests listed in the field e.g Peter, James, John it is possible to put a search in the form for "John" and it would retrieve all the entries where John appears whether first, second, or third without having to bring up all the records. Many thanks Peter "Newbie" wrote in message ... Are you referencing the field correctly? [Forms]![Guest Search] = a form called Guest Search. .. . . it is not telling it which control on that form to use therefore I think your query is saying select all records where blah blah like * or blah blah like * i.e. not actually filtering anything Trying adding the control name eg WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search]![txtName] & '*')); HTH "Peter Knight" wrote in message .com... Hello, I have a form called "Guest Search" where the user is asked to enter a guest name, which can appear in three different fields in a table. It is also possible that there are multiple entries for the guests in the field e.g Peter, James, John. What I need to do is to have the user enter their search in the guest search form and for the query to find all the entries which are a close match whether they are slightly mis-spelt or appear further down the field e.g James. I currently have the following SQL query, which I have put together from a query which is working, but only on exact matches and then adding suggestions from other posts. However the query doesn't work as it just displays all the results: SELECT SearchTable02.Guest, SearchTable02.[Artist Name], SearchTable02.[Pre-Mix DJ], SearchTable02.[Audio Details], * FROM SearchTable02 WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search] & '*')); Any help with this would be great.
|
|
#5
May 28th, 2004, 08:04 PM
|
|||
|
|||
|
Creating a wildcard search for a form - Searching for information in a field divided by commas?
Thanks Ben, it worked first time and does exactly what I want it to
do. Thanks again. Peter Ben Moore wrote in message ... Try adding another asterisk after the like in your where lines, such as: (((SearchTable02.[Audio Details]) Like '*' & [Forms]![Guest Search] & '*')); unless i don't understand this fully... in which case sorry... first attempt at answering a question... cheers, Ben Moore Peter Knight wrote: Thank you very much for the help. I now have it that if a search is carried out a * is entered in the search the results come up. What I am now after is a way of searching so that if there are the three guests listed in the field e.g Peter, James, John it is possible to put a search in the form for "John" and it would retrieve all the entries where John appears whether first, second, or third without having to bring up all the records. Many thanks Peter "Newbie" wrote in message ... Are you referencing the field correctly? [Forms]![Guest Search] = a form called Guest Search. .. . . it is not telling it which control on that form to use therefore I think your query is saying select all records where blah blah like * or blah blah like * i.e. not actually filtering anything Trying adding the control name eg WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search]![txtName] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search]![txtName] & '*')); HTH "Peter Knight" wrote in message .com... Hello, I have a form called "Guest Search" where the user is asked to enter a guest name, which can appear in three different fields in a table. It is also possible that there are multiple entries for the guests in the field e.g Peter, James, John. What I need to do is to have the user enter their search in the guest search form and for the query to find all the entries which are a close match whether they are slightly mis-spelt or appear further down the field e.g James. I currently have the following SQL query, which I have put together from a query which is working, but only on exact matches and then adding suggestions from other posts. However the query doesn't work as it just displays all the results: SELECT SearchTable02.Guest, SearchTable02.[Artist Name], SearchTable02.[Pre-Mix DJ], SearchTable02.[Audio Details], * FROM SearchTable02 WHERE (((SearchTable02.Guest) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Artist Name]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search] & '*')) OR (((SearchTable02.[Audio Details]) Like [Forms]![Guest Search] & '*')); Any help with this would be great.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
