<> doesnt work (1 Viewer)

[KenHigg]

Why does this not work:

Column with occasional 'x' but <> 'x' will not exclude them... ?

 

[jdraw]

Ken,
Please give us some more context. Is it possible the column has 'x' and some hidden/unprintable character?
Can you show your code and some sample records that are causing the issue?

 

[KenHigg]

Want to exclude these:

 

[jdraw]

Any chance you can post a small database exhibiting this issue? Just enough data and code to show the error.
Remove anything private/confidential.

 

[KenHigg]

Hum... it's really just a table with a column that has an occasional 'x' to indicate the need to exclude the row and a simple query with the criteria <> 'x'.

It is an attached excel table though, hum...

 

[KenHigg]

Here is watered down example, still does not work - ?

 

[plog]

Your example "doesn't work" in a different way than your initial post. Your initial post says your query is letting some with an 'x' into the results. Your example is excluding those without an 'x' as well as a correctly excluding those with an 'x'--it is not letting anything into the results.

Since you didn't demonstrate your initial post I can only explain your example: NULL. NULL is a tricky beast, it is not a value so it is impossible to compare it to anything, even negatively. NULL is not not 'x', so it fails to meet the cirteria and gets excluded.

What you really need to do is use the correct data type for your fields. Exclude should be a Yes/No field, not a text string. Then to exclude a field with a yes in the Exclude field you would use this criteria:

Exclude = False

 

[isladogs]

@plog beat me to it but try this query:

SELECT Table1.ID, Table1.exclude
FROM Table1
WHERE Not (Table1.exclude)="x" Or (Table1.exclude) Is Null;

Or this

SELECT Table1.ID, Table1.exclude
FROM Table1
WHERE Nz([exclude],"")<>"x";

 

[KenHigg]

A little wordy but sparked an idea, Is Null works - thanks! May have to trap empty len strs too...

 

[KenHigg]

yes, what isladogs suggest! Thanks

 

[jdraw]

I agree with the others NULL is the underlying issue.
Was trying isnull(exclude) = True to identify "wanted/keeper" records.

Glad you have a solution.

 

[KenHigg]

Thanks! I think I’ll just add a “” and use the <>

 

[KenHigg]

Smart folks here

 

[arnelgp]

your main concern is "x" is not being Excluded.
but you are also exclusing the Null.

because a Null can't be compared to Any value, you need
to trick it to have value like a Zero-length string ("").
then your query, excluding Only "x":

SELECT Table1.ID, Table1.exclude
FROM Table1
WHERE (((Table1.exclude & "")<>"x"));