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,
DickeyJohnContr oller.nActualAp plicationRate, Events.nAddress 1
FROM Events, DickeyJohnContr oller
WHERE Events.nTime >= #2007-01-05 16:47:00# AND Events.nTrimWeb EventID
= DickeyJohnContr oller.nTrimWebE ventID
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
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, Long Integer, Required
nActualApplicat ionRate 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
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, bigint, not allow nulls
nActualApplicat ionRate float, allow nulls
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,
DickeyJohnContr oller.nActualAp plicationRate, Events.nAddress 1
FROM Events, DickeyJohnContr oller
WHERE Events.nTime >= #2007-01-05 16:47:00# AND Events.nTrimWeb EventID
= DickeyJohnContr oller.nTrimWebE ventID
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
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, Long Integer, Required
nActualApplicat ionRate 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
DickeyJohnContr oller table
nTrimWebEventID ------ Primary key, bigint, not allow nulls
nActualApplicat ionRate float, allow nulls
Comment