Access query does not retrieve all records. Works fine in SQL Server.

[mrkselm]

Hi,

I am stuck with a problem in MS Access which does not occur in SQL
Server and I have been banging my head against the wall for a couple of
days now trying to resolve it. Namely, when I execute the following
query in Access not all records that fit the condition are returned,
and when I run this same query in SQL Server all the records I want are
retrieved.

The query is:

SELECT Events.nAsset, Events.nTime,
DickeyJohnController.nActualApplicationRate, Events.nAddress1
FROM Events, DickeyJohnController
WHERE Events.nTime >= #2007-01-05 16:47:00# AND Events.nTrimWebEventID
= DickeyJohnController.nTrimWebEventID
ORDER BY Events.nTime

In SQL Server this query is the same except for the ' character instead
of the # for dates.

Interestingly enough removing the time condition from the where clause
also does not return all expected records. It looks like the join is
the problem but I don't see what's wrong. There are no null values
among the nTrimWebIDs in either table.

I must be missing something very obvious. Your help is greatly
appreciated!

Mrdjan

Here are some details about the setup:

The query is hardcoded in a VB .NET application that connects to any
ODBC compliant DB. I just change my connection string and can run
either Access or SQL Server.
The data types in the MS Access database are as follows:
Events table:
nTrimWebEventID ------ Primary key, Long Integer, Required
nAsset Text, Not Required
nTime Date, Required
nAddress1 Text, Not Required

DickeyJohnController table
nTrimWebEventID ------ Primary key, Long Integer, Required
nActualApplicationRate Double, Not Required

By way of comparison, the SQL Server setup is as follows:

Events table:
nTrimWebEventID ------ Primary key, bigint, doesn't allow nulls
nAsset varchar(50), allows nulls
nTime datetime, does not allow nulls
nAddress1 varchar(50), allows nulls

DickeyJohnController table
nTrimWebEventID ------ Primary key, bigint, not allow nulls
nActualApplicationRate float, allow nulls

[Tim Marshall]

mr*****@hotmail.com wrote:

Interestingly enough removing the time condition from the where clause
also does not return all expected records. It looks like the join is
the problem but I don't see what's wrong. There are no null values
among the nTrimWebIDs in either table.

By "query in Access", you presumeably mean against an MS Jet database.
You are not using the correct ANSI format for the join. Try using the
excellent graphical MS Access query design view to do what you're doing.
You'll end up with something like:

SELECT
Events.nAsset,
Events.nTime,
DickeyJohnController.nActualApplicationRate,
Events.nAddress1
FROM
Events INNER JOIN DickeyJohnController ON Events.nTrimWebEventID
= DickeyJohnController.nTrimWebEventID
WHERE
Events.nTime >= #2007-01-05 16:47:00#
ORDER BY
Events.nTime
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me