468,457 Members | 1,765 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,457 developers. It's quick & easy.

Like '' & '' & ''

Having this description column in a table

"Cookies, peanut butter sandwich, regular"
When using this WHERE clause:

WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));

I dont get any results, however when using it this way round

WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));

I get the row as expected.

I assume that the query looks for them in order.

Is there a way i can get around this and make the query search for both
keywords no matter which order i ype them in.

Thanks
Dave

Jul 20 '05 #1
3 15409
[posted and mailed]

SkunkDave (da***********@totalise.co.uk) writes:
Having this description column in a table

"Cookies, peanut butter sandwich, regular"
When using this WHERE clause:

WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));

I dont get any results, however when using it this way round

WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));

I get the row as expected.

I assume that the query looks for them in order.


The query looks for a string that matches the pattern "*Peanut**Butter*",
so obviously it's not going to match "Butter Peanut".

As suggested by Harald Albrech this works:

WHERE (Food_Data.Description Like "*Butter*" or
Food_Data..Description Like "*Peanut*");

Note that the newsgroup you posted is devoted to MS SQL Server, and
the syntax you posted is not legal in SQL Server, but it looks that
it could be Access. Access has its own newsgroup, I believe the name is
comp.databases.ms-access. You may want to post there next time you
have a problem.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog wrote:
[posted and mailed]

SkunkDave (da***********@totalise.co.uk) writes:
Having this description column in a table

"Cookies, peanut butter sandwich, regular"
When using this WHERE clause:

WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));

I dont get any results, however when using it this way round

WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));

I get the row as expected.

I assume that the query looks for them in order.

The query looks for a string that matches the pattern "*Peanut**Butter*",
so obviously it's not going to match "Butter Peanut".

As suggested by Harald Albrech this works:

WHERE (Food_Data.Description Like "*Butter*" or
Food_Data..Description Like "*Peanut*");

Note that the newsgroup you posted is devoted to MS SQL Server, and
the syntax you posted is not legal in SQL Server, but it looks that
it could be Access. Access has its own newsgroup, I believe the name is
comp.databases.ms-access. You may want to post there next time you
have a problem.

Ive used the query suggested except ive used the word 'and' instead of
'or'. Works fine.

Yes the query is MS Access. I didnt see the Access newsgroup. I would
have posted it there if i did.

Out of curiousity, what would be the syntax for SQL Server?

Cheers

Jul 20 '05 #3
SkunkDave (da***********@totalise.co.uk) writes:
Out of curiousity, what would be the syntax for SQL Server?


WHERE (Food_Data.Description Like '%Butter%' or
Food_Data.Description Like '%Peanut%');

OR or AND depends on the desired result when only one string is included.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Collin VanDyck | last post: by
reply views Thread by Thomas Scheffler | last post: by
6 posts views Thread by Rich Wallace | last post: by
11 posts views Thread by Jeremy | last post: by
14 posts views Thread by Arne | last post: by
7 posts views Thread by John Nagle | last post: by
3 posts views Thread by gg | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.