Too many records missing after query applied

[jvleigh221]

Here is the SQL in a generic form:

SELECT TABLE.FIELD1, TABLE.FIELD2, TABLE.FIELD3
FROM TABLE
WHERE (((TABLE.FIELD3)<>"CRITERIA1" And (TABLE.FIELD3)<>"CRITERIA2" And (TABLE.FIELD3)<>"CRITERIA3" And (TABLE.FIELD3)<>"CRITERIA4"));

This SQL statement is used as an Access form's Record Source.

However, now the records that may have an empty or null value for Field3 are excluded, as well.

Any suggestions are apprciated.

 

[jsteph]

You could enclose the entire existing WHERE clause in parens, and then append OR IS NULL.

Also, you could use the below WHERE clause example:
where trim("" & TABLE.FIELD3)<>"CRITERIA2"
...etc.

The Trim("" & [field]) takes care of nulls and ""
-Jim

 

[jvleigh221]

I tried the trim and it did the same this. It excluded all record with an empty string.

I need to make sure they are not excluded.

thanks

 

[PHV]

And what about this ?
SELECT TABLE.FIELD1, TABLE.FIELD2, TABLE.FIELD3
FROM TABLE
WHERE Nz(TABLE.FIELD3,'?') Not In ('CRITERIA1','CRITERIA2','CRITERIA3','CRITERIA4');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jvleigh221]

I re-examined the code and with some extra help it worked when I did the following:

WHERE (((TABLE.FIELD3)<>"CRITERIA1" And (TABLE.FIELD3)<>"CRITERIA2" And (TABLE.FIELD3)<>"CRITERIA3" And (TABLE.FIELD3)<>"CRITERIA4")) Or (((TABLE.FIELD3) Is Null));

Thanks to all who helped.

Jeremy