Trouble with SQL statement 1

[Glohamar]

I am listing records in a listbox using SQL in VBA code. I am needing to filter the records by a column that has the following values "Complete", "Rejected", "Transferred", "Working", "Hold", or could even be Null.

What I want is to only display the records that are not Rejected or Transferred. I thought it would be easier to have the SQL read

SELECT * FROM tbl WHERE Action <> "Rejected"

but when I try to add the other parameter it does not work. Like this...

SELECT * FROM tbl WHERE Action <> "Rejected" OR Action <> "Transferred"

The returned records show records that are rejected, but not ones transferred.

Can someone help. I am hoping that I do not have to list out all the parameters that I want to show, but tell the query to just not show certian records.

Thanks

Dave

 

[PHV]

SELECT * FROM tbl WHERE Nz(Action,'') Not In ('Rejected','Transferred')

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

 

[Glohamar]

That is it PHV. That is the answer.
Thanks so much.

Dave

 

[fkegley]

WHERE Action <> "Rejected" OR Action <> "Transferred"

When you put in this WHERE clause, you forgot that computers do what they are told to do, not what you want them to do.

If Action is 'Rejected' then that is not = 'Transferred' and vice versa. That is why you were getting more records than you should have.

Another correct WHERE clause would be:

WHERE Action <> "Rejected" AND Action <> "Transferred"


Frank kegley
fkegley@hotmail.com