Got a question about technology? Ask it here. Discussion of hardware, software, TiVos, multi-region DVDs, Windows, Macs, LINUX, hand-helds, iPods, anything tech related. Better than any helpdesk!
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.
MySQL doesn't work that way...
(hence my confusion - sorry ita!)
I have really old Access skills ...
SELECT colA, colB, * FROM TableA WHERE ISNULL(colA=colB)
have you tried using design view to build a simple query? Your sql should work, but using design view might help.
I started it in Design view, and then switched to SQL view to enter my specifics. Then, when it was weird, switched back to Design view where everything looked theoretically normal. I'm not sure of any other way to enter it into Design view than like:
Field:TableA.*colAcolB
Table:TableATableATableA
Sort:
Show:YYY
Criteria:<>[colB]
(and it'll be a miracle if that table comes out right...)
The Query Designer will get it wrong.
That design
should
work, but you might also try creating a fourth field that's an expression, like expr1: [colA] = [colB], and then have the criteria in the field be equal to false.
If you have nulls in either column, this is the query in Oracle:
select colA, colB, r.* from table r
where nvl(colA,'NOTMATCHA') != nvl(colB,'NOTMATCHB')
I think mysql's version of the nvl function is ifnull, but I don't have mysql here to test:
select colA, colB, * from table
where ifnull(colA,'NOTMATCHA') != ifnull(colB,'NOTMATCHB')
