Equal or not equal

[as0125]

Hello all,

I'm having a bit of trouble trying to understand why the following two queries return empty sets:
----------------------------------------------------------
SELECT *
FROM A, B
WHERE A.ActualRelease <> B.ActualRelease;
----------------------------------------------------------
SELECT *
FROM A, B
WHERE A.ActualRelease = B.ActualRelease;
----------------------------------------------------------

To the best of my understanding, two values are either equal or not equal - so I should get a recordset for one query or the other.
1. "ActualRelease" is set up as a text field in a table.
2. Table A is a table containing historical data.
3. Table B is a temp table, created to store incoming data temporarily.
4. Both tables A and B share some common column names.
5. Ultimately, what I want to do is compare values in a column between tables A and B. If values are NOT equal, update table A's "ActualRelease" with table B's "ActualRelease" value.

I've managed to put together an Update query, that works only sometimes. I've narrowed down my problem to the above queries. Though I consider myself a novice at this, I suspect there is a problem in the way Access evaluates null fields versus fields that had values that got deleted. Let me explain:

1. When I first ran my update query, Table A's "ActualRelease" field was updated with a correct value, based on Table B's "ActualRelease" value.
2. Because I wanted to retest the query, I went to Table A and deleted the value in "Actual Release".
3. When attempting to rerun the update query, Access did NOT return a recordset for TableA.ActualRelease<>TableB.ActualRelease.

Any help/suggestions?

Thanks!!!

 

[PHV]

Replace this:
WHERE A.ActualRelease <> B.ActualRelease
By this:
WHERE Nz(A.ActualRelease) <> B.ActualRelease

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

 

[cmmrfrds]

A test such as A.ActualRelease <> B.ActualRelease is a test for a criteria or not. Null is not a value it is an unknown so it will not qualify when testing for a criteria. You need to explicitly look for Null.
examples: A.ActualRelease IS NULL
Not(A.ActualRelease IS NULL and B.ActualRelease IS NULL)
etc...

 

[as0125]

that worked!!! thanks a lot!

just curious now, WHY that worked? what does Nz mean?

 

[as0125]

cmmrfrds posted a reply that i didn't see when i posted mine.

i should clarify. what worked for me was PHV's suggestion.