Multiple values in a WHERE ... LIKE statement

[jonnyp]

Hello there!

I started using Access last week at work and am starting to get to grips with it. I have a lot of experience with PHP/mySQL setups but never really used Access or VBA before.

I've built a form to export a set of queries to excel, based upon the date input, while excluding a certain search keyword on the form, and all is working well.

It scans lists of keywords used on Google to find a client's website and reports the traffic generated as a result. We are excluding the client's company name so we only report traffic for generic terms eg 'digital camera' rather than '*canon*'

This is working fine, but we have a client that has multiple company names we would like to exclude. In mySQL you can use a query like
"WHERE keyword NOT LIKE '%canon%|%sony%' ", where the pipe (|) character represents 'OR', so it will exclude searches that include 'canon' or 'sony'. This doesn't seem to work in Access.

Is there an equivalent character in Access SQL, or could I use a function to replace the pipe character with '" AND keyword NOT LIKE "' dynamically in the query string? If so, any hints on how I might do this?

Many thanks!

Jonny

 

[namliam]

Nope sorry no such character in Access

 

[raskew]

Hi -

Take a look at this http://www.access-programmers.co.uk/forums/showthread.php?t=154683 in the Code Repository. It's intended to create a query to search for multiple criteria as input by the user.

Think you can modify it with minimum problem to exclude multiple criteria.

Give it a try and please post back.

Best Wishes - Bob

 

[jonnyp]

Hi Bob,

Thanks for that, this looks great. Ideally I'd like to edit the existing queries as little as possible rather than creating new ones, so I was wondering if I can tweak what I have from

WHERE keyword NOT LIKE Get_ExcludeTerm()

to something like

WHERE Get_ExcludeSQL()

and paste in the whole WHERE SQL statement rather than just a parameter? Forgive me if this is a basic question, I only recently discovered I can dynamically replace a parameter with a function

Thanks,
Jonny

 

[jonnyp]

Incidentally, is InStr faster than LIKE, and is it case-sensitive?

Cheers!

 

[namliam]

Incidentally, is InStr faster than LIKE, and is it case-sensitive?

No and no

The other stuff I will leave for bob to answer as it is his code.... but in general:
Where Function() doesnt work in Access SQL.

You can do something like that if you feed the complete SQL into the query... but dont know if you will want to do that.

 

[raskew]

If you apply GetStuff, selecting:
table/query: Customers (from Northwind)
Field: CompanyName
Items: store,grocer,market

it produces this query def:

SELECT customers.*
FROM customers
WHERE (((InStr([companyname],'grocer'))[COLOR="Red"]>[/COLOR]0)) OR (((InStr([companyname],'market'))[COLOR="red"]>[/COLOR]0)) OR (((InStr([companyname],'store'))[COLOR="red"]>[/COLOR]0));

A slight revision to exclude: store,grocer,market would produce:

SELECT customers.*
FROM customers
WHERE (((InStr([companyname],'grocer'))[COLOR="red"]=[/COLOR]0)) [U]AND[/U] (((InStr([companyname],'market'))[COLOR="red"]=[/COLOR]0)) [U]AND [/U](((InStr([companyname],'store'))[COLOR="red"]=[/COLOR]0));

Bob

 

[namliam]

AND dont forget to change the OR to an AND... otherwize you are just excluding one of the three.