Bug in access query engine?

[MrDeej]

This SQL



returns these values



Anybody have an idea of what causes this?

[Stewart Ross]

Strange behaviour indeed, Mr Deej. At first glance it looks like a bug. Does the same happen if you change the WHERE clause to
WHERE LSLGNR like 'TDO', or
WHERE LSLGNR IN ('TDO')?

It should make no difference to the end result, which of course should NOT include the value TDN as the equality version appears to be doing and for which I have no explanation. Testing the alternatives may help tie down what this 'bug' is.

-Stewart

[MrDeej]

Hmm.. same result when i us the "WHERE LSLGNR IN ('TDO')"

To make the confusing even bigger i get only 'TDO' result when i use the group by option in the query. This way i actually can use it, but still dont like Access having such bugs.

[dsatino]

You need a GROUP BY clause. I know it doesn't seem to make sense that you would, but it has something to do with the way Access interacts with SQL servers.

[MrDeej]

For this database it worked with group by. It seems a little slower to show the results, but it works.

However, i have a different database on another computer, and this one works without the group by clause on a similar query to the same table/server. It is also some difference in service packs and hotfixes on that access installation.

Maybe it is a difference in somehow access communicates with sql server, OLE DB and ODBC ?

[Stewart Ross]

Only other thing to add is that you could try a pass-through query which lets SQL-server do the work; the Group By clause in the Access query will force Access to fetch all matching rows from the SQL-server back end before it can group the query (hence it will indeed be slower). I'd guess that the SELECT query in Access is not fetching all matching rows when executed, which really SQL-Server will do much faster.

-Stewart