Creating a wildcard search for a form.

  • Thread starter Thread starter Peter Knight
  • Start date Start date
P

Peter Knight

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.
 
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] &
Click to expand...
'*'));


HTH


Peter Knight said:
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.
Click to expand...
 
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 said:
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] &
Click to expand...
'*'));


HTH


Peter Knight said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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] &
Click to expand...

'*')) OR
(((SearchTable02.[Pre-Mix DJ]) Like [Forms]![Guest Search]![txtName] &
Click to expand...

'*')) OR
(((SearchTable02.[Audio Details]) Like [Forms]![Guest Search]![txtName] &
Click to expand...

'*'));


HTH


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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ben, it worked first time and does exactly what I want it to
do. Thanks again.

Peter

Ben Moore said:
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 said:
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 said:
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



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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top