Like '' & '' & ''

[SkunkDave]

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

[Erland Sommarskog]

[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

[SkunkDave]

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

[Erland Sommarskog]

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