Criteria <>

  • Thread starter Thread starter Bryan
  • Start date Start date
B

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?
 
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"
 
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
 
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"
 
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 ..


Rick Brandt said:
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 said:
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"
Click to expand...
Click to expand...
 
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"


JohnFol said:
If you enter the WORD "PAID" and then compare agains
Click to expand...
get nothing as "PAID" can never not be "PAID".

You need something like

Select * from MyTable where TestField <>"PAID"
Click to expand...


.
Click to expand...
 
Back
Top