Are there null values involved frelling things up somehow?
You're like my fairy godmother, and Santa Claus, and Q all wrapped up into one! Q from Bond, not Star Trek.
Buffy ,'Help'
Buffistechnology 3: "Press Some Buttons, See What Happens."
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 S. - May 04, 2007 8:16:53 am PDT #1477 of 25496
"Faded like the lilac, he thought."
DXMachina - May 04, 2007 8:25:29 am PDT #1478 of 25496
You always do this. We get tipsy, and you take advantage of my love of the scientific method.
Null values would screw up
SELECT colA, colB, * FROM TableA WHERE colA=colB
too though, no?
eta: Apparently not...
§ ita § - May 04, 2007 8:26:40 am PDT #1479 of 25496
Well not canonically, no, but this is transformative fiction.
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?
§ ita § - May 04, 2007 8:28:55 am PDT #1480 of 25496
Well not canonically, no, but this is transformative fiction.
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.
§ ita § - May 04, 2007 8:34:33 am PDT #1481 of 25496
Well not canonically, no, but this is transformative fiction.
::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.
Jon B. - May 04, 2007 8:35:39 am PDT #1482 of 25496
A turkey in every toilet -- only in America!
ita - have you tried using design view to build a simple query? Your sql should work, but using design view might help.
hippocampus - May 04, 2007 8:38:14 am PDT #1483 of 25496
not your mom's socks.
MySQL doesn't work that way...
(hence my confusion - sorry ita!)
I have really old Access skills ...
Gudanov - May 04, 2007 8:43:11 am PDT #1484 of 25496
Coding and Sleeping
SELECT colA, colB, * FROM TableA WHERE ISNULL(colA=colB)
§ ita § - May 04, 2007 8:44:13 am PDT #1485 of 25496
Well not canonically, no, but this is transformative fiction.
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...)
Gudanov - May 04, 2007 8:44:59 am PDT #1486 of 25496
Coding and Sleeping
The Query Designer will get it wrong.