CODEWITHSUNDEEP
ms-access
Mikjord
Mikjord

Reputation: 3

like and not like in access query

I want user to be able to filter results for a query to either INclude or EXclude a string based on value in a checkbox. The Checkbox is referenced correctly and when checked does indeed filter just on Hackney, however when unchecked the "not" part gives (wrongly) zero records

IIf([Forms]![Navigation]![TEST]=True,"Hackney",Not In ("Hackney"))

Any suggestions very welcome, been trying different options all morning.

Upvotes: 0

Views: 135

Answers (2)

Mikjord
Mikjord

Reputation: 3

Excellent stuff thanks so much that works a treat. In fact actually works fine when you look at it through Query Builder (which suits better for a variety of reasons).

For anyone else struggling with this I've taken it a bit further after this tip and linked the like/not like to a value in a combo, rather than the hard coded "Hackney" mentioned above. Also works in Query Builder, this gives my users a lot of flexibility in running their own queries properly.

Thanks again. The SQL for this is:

SELECT Clients.CLIENTSID, Clients.FirstName, Clients.LastName,     
        LondonBoroughs.LondonBorough, Clients.LondonBoroughID
FROM LondonBoroughs INNER JOIN Clients 
    ON LondonBoroughs.LONDONBOROUGHSID = Clients.LondonBoroughID
WHERE (((Clients.LondonBoroughID) Like ([Forms]![Navigation]![ReportsLondonBoroughCOMBO])) 
    AND (([Forms]![Navigation]![TEST])=True)) 
OR (((Clients.LondonBoroughID) Not Like ([Forms]![Navigation]![ReportsLondonBoroughCOMBO])) 
    AND (([Forms]![Navigation]![TEST])=False));

Upvotes: 0

parakmiakos
parakmiakos

Reputation: 3020

You can not do what you are trying to do. You are trying to build your SQL query through this IIf statement.

Based on what I believe you want, what would work in your case is the following:

select ... /* your select fields */
from ....  /* your table(s) */
where .... /* the rest of your conditions */
and (
    ([FieldName] = "Hackney" And [Forms]![Navigation]![TEST] = True) 
    or 
    ([FieldName] Not In ("Hackney") And [Forms]![Navigation]![TEST] = False)
)

Upvotes: 1

Related Questions