Qry excluding a word not working (1 Viewer)

[Harris@Z]

Hi, I hope someone can shed a light on this - seems so simple yet the answer alludes me.

This works in a form query:
SELECT Contact_People.* FROM Contact_People WHERE (((Contact_People.Category)="Accounts"));

However, I want to have the record source exclude "Accounts" from the field Category, I have no records displayed.

These are the two methods I tried.
SELECT Contact_People.* FROM Contact_People WHERE ((Not (Contact_People.Category)="Accounts"));
SELECT Contact_People.* FROM Contact_People WHERE (((Contact_People.Category)<>"Accounts"));

Seems to me to be correct, so what am I missing.

H

 

[theDBguy]

Could be with the data. You may have to post a sample db with test data to show the problem. What other categories are there?

 

[Gasman]

Upload your dB, as I would expect the latter code to work. That is what I would have tried.

 

[jdraw]

I agree that readers need to see your table design and some data. Is it possible there is a spelling mistake?

You may try

Select distinct Category from Contact_People;

to see existing Categories.

 

[Harris@Z]

Thanks for your responses. The dB is fairly large, and connected to a sql server backend

 

[plog]

SELECT Contact_People.* FROM Contact_People WHERE (((Contact_People.Category)<>"Accounts"));

This should work as long as there is data in Contact_People.Category. It will not include NULL values of Contact_People.Category. You must specifically address NULL values.

 

[Gasman]

Thanks for your responses. The dB is fairly large, and connected to a sql server backend

So create a local table and try on that, if for nothing else than testing where the fault lies.

 

[Pat Hartman]

To help you with the syntax for getting nulls returned as well as strings with different values:

SELECT Contact_People.* FROM Contact_People WHERE (Not Contact_People.Category="Accounts") Or (Contact_People.Category Is Null);

 

[The_Doc_Man]

Just an attempt to help you have to type less...

Since your SELECT clause has only a single table as its source, you do not have to qualify it by including the table name in the elements of the WHERE clause. There is only one place from where those fields can come. Note that the moment you deal with JOIN clauses or sub-queries that is no longer the case. But this suggestion might help you have less typing to do at least some of the time.

 

[Gasman]

Really looks like the query design option was used?

 

[Harris@Z]

Thanks for your inputs!
These are two records in the table - identical except the one has "Accounts" in the Category column.

Contact_ID	Company_ID	FirstName	LastName	Telephone	email	Category
11843	3524	Ilona	Doggons	78014​	ilona@naturalc.com	Accounts
11866	3524	Ilona	Doggons	78014​	ilona@naturalc.com

If my Select statement is for Category ='Accounts' the result is 1 record
e.g. SELECT Contact_People.* FROM Contact_People WHERE Category = "Accounts"

If my Select statement is for Category <>'Accounts' the result is 0 records
If my Select statement is for Category not 'Accounts' the result is 0 records
e.g. SELECT Contact_People.* FROM Contact_People WHERE Category<>"Accounts"

This is the weirdest!

 

[Gasman]

Not really, as the other row is likely Null, as previously explained?

 

[jdraw]

Did you try the query I suggested in #4? It should show the unique values in Category.

 

[theDBguy]

If my Select statement is for Category <>'Accounts' the result is 0 records

If the category is Null, then it cannot be compared to anything (whether equal to or not equal to). You'll have to handle nulls in a special manner as already described earlier. I asked earlier what other categories were there. I guess you missed it. If there's only "accounts" and nothing else, then the result you're getting makes sense.

 

[Harris@Z]

Thanks for all your input, much appreciated!

Apologies for not responding to all the questions.
Yes, there are other categories, e.g., Quality Manager, Food developer, etc., although most have no entry.

I will evaluate considering null in my query for I appreciate that it may be as simple as that!

 

[Harris@Z]

Just an attempt to help you have to type less...

Since your SELECT clause has only a single table as its source, you do not have to qualify it by including the table name in the elements of the WHERE clause. There is only one place from where those fields can come. Note that the moment you deal with JOIN clauses or sub-queries that is no longer the case. But this suggestion might help you have less typing to do at least some of the time.

Thank you, a great lesson! I tend to forget to keep the code 'clean'!

 

[Harris@Z]

I included the Is Null in my recordsource, and that resolved the issue.
I am grateful to all your input, and particular for the solution (which is so obvious in retrospect!)

Thanks all for your time in guiding and helping me.
Harris