Use of 'not' in specifying a critieria in a query

[Pat Strauss]

I have been using Access for many, many years, but it keeps changing the rules...
I use to be able to put 'not' in front of a word or phrase that was likely to appear in a field when running a query. The instance could be 'not London' if wanting to display, say, people or companies that didn't have 'London' in their address.
I don't know what I'm doing differently, but I can't seem to do that now. I can use 'like' but not the opposite request.
It's not a true / false field, more a filter to display records where a word is not used. If I could get that far, I would be willing to create a new table of the results, but I don't know how to do that from the results of a query.

Can anyone help?
Pat

[CJ_London]

I use to be able to put 'not' in front of a word or phrase that was likely to appear in a field when running a query.

Are you talking about the sql language? In which case which version? Or vba or some other language?

[Welshgasman]

Cannot see how TBH.
If you are looking for anywhere in the address you would need *London*, if at the start London*, if at the end *london

For single values in a field = or <>

SELECT tblDaily.*, tblFoods.FoodName
FROM tblDaily INNER JOIN tblFoods ON (tblDaily.FoodIDFK = tblFoods.FoodID) AND (tblDaily.FoodIDFK = tblFoods.FoodID)
WHERE (((tblFoods.FoodName) Not Like "Choco*"));

[Micron]

Maybe show some examples of what doesn't work (and/or what you get), and what you expect.
You should be able to use Not LIKE 'text' but you will need wildcards if you have "London, UK". You can't eliminate that with Not "London".

[Pat Strauss]

I presume sql - whatever Access uses by default. This is a simple database, not linked with any other.

[madpiet]

Maybe provide us a spreadsheet or Access table with some records and a few concrete examples of what you want?

Assume Address(StreetAddress, City, County...)

SELECT *
FROM Addresses
WHERE City <> "London";

WHERE City Not LIKE '%London%'
in Murica would omit cities like "New London" and "Londonderry"

[Pat Strauss]

Just tried your suggestion of <>London and it worked!!
Many thanks.

[mike60smart]

You can also use Not In("London")

[madpiet]

You can also use Not In("London")

You can, but the standard use of NOT IN is with a list of values.

NOT IN ("London", "Belfast", "Edinburgh")

and not just a single value.

[mike60smart]

Hi Madpiet
Very True

[Minty]

NOT IN is allegedly a poor choice in big datasets, as it doesn't make very good use of indexes (if at all).

[Gicu]

Have you tried to use the Instr() function like so:

Instr([YourAddressField],"London")=0 (=not London)

Cheers,