Access 2003 Query Bug?

[Bart Willems]

A coworkers database is showing problems when querying the ID
(autonum, long int) field. For instance:
SELECT * FROM tbl_ref WHERE code=237
will not return *anything*, but
SELECT * FROM tbl_ref WHERE code<>237
will correctly return all rows except the ones with code 237.

In similar fashion running an inner join on the code field will return
0 records:
SELECT tbl_people.* FROM tbl_people INNER JOIN tbl_ref ON
tbl_people.role = tbl_ref.code
But, turning the inner join into a left join will make things run just
fine:
SELECT tbl_people.* FROM tbl_people LEFT JOIN tbl_ref ON
tbl_people.role = tbl_ref.code


The why and how of this bug puzzles me. I'm starting to find
workarounds so that's not the issue, I'm just curious as to what is
going on and if this is a known issue in Access 2003. Any clues
anyone? Bueller?

 

[John Spencer]

It is possible that the index is corrupted. Can you drop the index, compact,
and rebuild the index.

You could test if the index is the problem by constructing a query that won't
use the index. I think something like the following will do that

SELECT * FROM tbl_ref WHERE

 * 2 = 237 * 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bart Willems]

John,

Thank you for your response, I will try that. I tried compact & repair
but I didn't drop the index, so I'll give that a try. Forcing the JET
engine to look outside the index with the formula is also a good idea.
I'll give it a shot and let you know.

Regards,
Bart

 

[Bart Willems]

John,

Thank you, that fixed it. Checking for

 + 0 = 237 was already
enough to identify the culprit. "Dropping the index" was a bit more
involved as it was one of those hidden indexes made by access to speed
up queries (even though I had indexed the field by myself as well) so
I ended up dropping the table, run compact & repair and import the
table from a csv file. That did the trick.

Regards,
Bart

 

[ucalcen]

Cannot see the criteria in a particular query Access 2003 and I don't want to redo it....My others queries are OK. Thanks