Just guessing/hoping that Access query structure isn't totally kinked - wouldn't:

SELECT colA, colB, * FROM TableA;

Return everything from each column

and

SELECT colA, colB, * FROM TableA WHERE colA=colB;

Return matches?

What are you trying to query?

Why wouldn't SELECT colA, colB, * FROM TableA return more rows than SELECT colA, colB, * FROM TableA WHERE colA=colB?

If there are no rows where colAcolB (as implied by the first query returning nothing), then those two queries should return the same results, yes?

My big problem is that I know records satisfy the very first query. I can see them. I just can't work out how to return only those.

Oh! Right! The board ate my <> in the first query. It should read

SELECT colA, colB, * FROM TableA WHERE colA<>colB;

That makes sense now.

How about SELECT colA, colB, * FROM TableA WHERE NOT(colA=colB)?

How about SELECT colA, colB, * FROM TableA WHERE NOT(colA=colB)?

Empty set, I'm afraid.

Am I making an error in translation or syntax? I do hate Access, but this is extra frustrating.

Are there null values involved frelling things up somehow?

Null values would screw up

SELECT colA, colB, * FROM TableA WHERE colA=colB

too though, no?

eta: Apparently not...

Liese, that's exactly it!

I find my 4K+ records quite precisely with an Is Null criterion.

Access Help says:

If Null values exist in a field, they can affect the query's results

Well, of course they're supposed to affect the results. Values in fields do that. It's what queries are to reflect.

However, and I can't swear to it, MySQL doesn't work that way, I think. And Access Help doesn't say anything on that, the most obvious help page so far on which to mention it, that it would mess with comparison expressions.

Logic syntax wise, !(a=b) == a!=b, right?

Null values would screw up SELECT colA, colB, * FROM TableA WHERE colA=colB too though, no?

Doesn't look like there are any values in colB that are null. Now, I did try switching the order in the expression of colA and colB (frustrated and bored) and that didn't fix it either.

Grr.

Funnily enough, a coworker told me that colA==colB, and I knew it wasn't because I'm working on reconciling data. So when the query agreed with him, I got paranoid.

When I couldn't flip the one query and get the correct results, I began to wonder if he was using Jedi mind powers or something to mess with me.

::returns from quick test::

Ayup. NULL isn't equal to NULL either. And having NULL in either colA or colB is making it night impossible to return that row with a criterion that compares the two.

ita - have you tried using design view to build a simple query? Your sql should work, but using design view might help.