wildcards in the like statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi there,
At the moment i am trying to make a query where i am able to search a field
"search_criteria". This field contains a bunch of keywords that is related to
the record's contents. I keep having a problem with my SQL though, as i try
to place a parameter query in surrounded by wildcards, it says i have invalid
syntax. Below is the code that i am trying to use:

SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*[Search string for]*"));

I know that it is my LIKE statement that is the problem, but from what i can
see it follows all the syntax that i have seen related to the LIKE statement.

Thanks for your help
 
Try this:


SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*" & [Search string for] &
"*"));
 
You my man, are a man of miracles!!!
Can you tell me the function of the & operators and when i should use them?
i couldnt find anything on & operators in the Access help files.

Thank you very much again

Ken Snell said:
Try this:


SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*" & [Search string for] &
"*"));


--

Ken Snell
<MS ACCESS MVP>


Matt Malone said:
hi there,
At the moment i am trying to make a query where i am able to search a
field
"search_criteria". This field contains a bunch of keywords that is related
to
the record's contents. I keep having a problem with my SQL though, as i
try
to place a parameter query in surrounded by wildcards, it says i have
invalid
syntax. Below is the code that i am trying to use:

SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*[Search string for]*"));

I know that it is my LIKE statement that is the problem, but from what i
can
see it follows all the syntax that i have seen related to the LIKE
statement.

Thanks for your help
Click to expand...
Click to expand...
 
& is used to concatenate (join) strings. For example. If [FirstName] =
"Matt" and [LastName] = "Malone", [FirstName] & " " & [LastName] would give
Matt Malone. Another place where this is used frequently is: [City] =
"Buffalo", [State] = "NY" and [Zipcode] = "16015"; [City] & ", " & [State] &
" " & [Zipcode] would give Buffalo, NY 16015. These examples might be used
as calculated fields in a query that was the recordsource for a label
report.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com





Matt Malone said:
You my man, are a man of miracles!!!
Can you tell me the function of the & operators and when i should use them?
i couldnt find anything on & operators in the Access help files.

Thank you very much again

Ken Snell said:
Try this:


SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*" & [Search string for] &
"*"));


--

Ken Snell
<MS ACCESS MVP>


Matt Malone said:
hi there,
At the moment i am trying to make a query where i am able to search a
field
"search_criteria". This field contains a bunch of keywords that is related
to
the record's contents. I keep having a problem with my SQL though, as i
try
to place a parameter query in surrounded by wildcards, it says i have
invalid
syntax. Below is the code that i am trying to use:

SELECT tbl_furn_item.supplier, tbl_furn_item.range,
tbl_furn_item.description, tbl_furn_item.price, tbl_furn_item.rrp,
tbl_furn_item.search_criteria
FROM tbl_furn_item
WHERE (((tbl_furn_item.search_criteria) LIKE "*[Search string for]*"));

I know that it is my LIKE statement that is the problem, but from what i
can
see it follows all the syntax that i have seen related to the LIKE
statement.

Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top