Query Criteria Using And function

[geaker]

I have a field named Status in a table. It is defined as "text", 1 character, and default "". Validation rules are must be "" or P or M. Required set to "No', Zero Length set to "Yes". I am trying to pull all records that are not equal to P or M.

I have tried <>&quot;P&quot; And <> &quot;M&quot;, <>&quot;P&quot; or <> &quot;M&quot;. The or retuns records with the Status = P and the &quot;And&quot; returns no record. Anyone have any ideas?

Thanks,
Gerald

 

[dodge20]

Try this

select *
from table
where Status not in ('P','M')

Dodge20

 

[geaker]

Dodge,
Thanks for the try but same result as the 'and' function. No records are returned. I have looked at the data in the table and know that &quot;Status&quot; has other than P or M in it. I have also tried the IsNull and IsEmpty.

Thanks

 

[dodge20]

I am confused. I gues you could try:

select * from table
where status = &quot;&quot;

Good luck

Dodge20

 

[geaker]

Dodge,
Still no luck. I think I will make the default a 'U', lol. At least that way I can match.

Thanks for the input,
Gerald

 

[swampBoogie]

select *
from t
where Status not in ('P','M') 
   or Status is null

 

[geaker]

swampBoogie,
Tried that with the same results.

Thanks,
Gerald

 

[CosmoKramer]

I guess I'm confused....Validation rules are must be &quot;&quot; or P or M. I am trying to pull all records that are not equal to P or M.

Wouldn't the remaining records that are not equal to P or M be &quot;&quot; or null??

Why don't one of these statements work?:

select * from table 
where status = ""

which dodge20 already suggested
or

select * from table 
where status Is Null

 

[geaker]

I know what y'all are saying. It had me stumped too. I finally changed the default to 'U' for unpaid and it works.

Thanks for the help,
Gerald