Criteria <>

[Bryan]

I have a query with three fields...
Ordernumber, OrderDate, and Test
All coming from the same table

In the text field I fill in the word PAID.
When I enter <>"PAID" or <>'PAID' in the criteria for the
Test field...I don't get anything from executing the query.

Some of my records have PAID in the Test field and others
don't. Shouldn't there be some records that popup?

 

[Dan Artuso]

Hi,
You should get records returned.
Post the SQL for your query.

 

[JohnFol]

If you enter the WORD "PAID" and then compare agains <>"PAID", of course you
get nothing as "PAID" can never not be "PAID".

You need something like

Select * from MyTable where TestField <>"PAID"

 

[Bryan]

SELECT Orders.OrderDate, Transactions.OrderNumber,
Orders.Company, Orders.Address, Orders.City, Orders.State,
Orders.Zip, Orders.BalanceDue, Orders.FinalProductTotal,
Customers.Text1 AS [Sales Person], Transactions.Amount,
Transactions.Date, Orders.LocalSortText1,
Orders.LocalSortCurrency1 AS [Commission Paid],
Orders.LocalSortCurrency2 AS [Commission Pending],
Orders.LocalSortDate1 AS [Commission Pay Date]
FROM (Orders INNER JOIN Customers ON Orders.CustomerID =
Customers.CustomerID) INNER JOIN Transactions ON
Orders.OrderNumber = Transactions.OrderNumber
WHERE (((Transactions.Amount)>0) AND
((Orders.LocalSortText1)<>"Paid"));

Dan,
I played with this a little more... there is something
going on with Access and the LocalSortText1 field. I've
run this query with the <> on the Company field and it
works (for some company name that exists in my table and
it doesn't return that company name, but returns
ecerything else. This is correct). For some reason...
Access is not seeing the LocalSortText1 field. It's
defined as a text field. I'm totally lost on this. I

 

[Rick Brandt]

I don't believe that is the problem.

If the only entries you have in the table are those with "PAID" and those with
Null then your criteria of <> "PAID" will return blank as you cannot compare a
Null to anything. Try your query with entries of both "PAID" and "UNPAID" and
it will work. Alternatively, you could wrap an Nz() function around the field
being tested.

Select * from MyTable WHERE NZ([TestField],"") <> "PAID"

 

[JohnFol]

Rick, I was basing this on the comment of

"In the text field I fill in the word PAID"

It's ambiguous if this is the text field in the query grid, or the field in
the table, but seing as it's a probable explanation I thought I'd offer it.
Still like to see the SQL though ..

I don't believe that is the problem.

If the only entries you have in the table are those with "PAID" and those with
Null then your criteria of <> "PAID" will return blank as you cannot compare a
Null to anything. Try your query with entries of both "PAID" and "UNPAID" and
it will work. Alternatively, you could wrap an Nz() function around the field
being tested.

Select * from MyTable WHERE NZ([TestField],"") <> "PAID"

If you enter the WORD "PAID" and then compare agains <>"PAID", of course you
get nothing as "PAID" can never not be "PAID".

You need something like

Select * from MyTable where TestField <>"PAID"

 

[Bryan]

You were right... can't compare null... if put in the nz
function and it worked... appreciate the help guys.

-----Original Message-----
I don't believe that is the problem.

If the only entries you have in the table are those with "PAID" and those with
Null then your criteria of <> "PAID" will return blank as you cannot compare a
Null to anything. Try your query with entries of both "PAID" and "UNPAID" and
it will work. Alternatively, you could wrap an Nz() function around the field
being tested.

Select * from MyTable WHERE NZ([TestField],"") <> "PAID"

If you enter the WORD "PAID" and then compare agains

get nothing as "PAID" can never not be "PAID".

You need something like

Select * from MyTable where TestField <>"PAID"

.