Re: Different results in SQL and Access

How about:
trandate = #02/06/2008#
unless for some reason it is necessary to omit the first second of the day.
This suggests a reason why you are getting different results. The Date function stores the date as 12:00:00 AM. It coud be that such a date is being handled differently in the two systems (although I don't quite follow how a table is linked to a table, but maybe that's because of my unfamiliarity with SQL Server).

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:ujYiduq1IHA.1240@xxxxxxxxxxxxxxxxxxxxxxx
Pat Backowski wrote:
Hi Everyone,

When I run the query
Select a,b,c from mytable where trandate >= #20/06/2008 00:00:01# and
trandate <= #20/06/2008 23:59:59# and (trantype = "OP" or trantype =
"NP);

in SQL Server, I get the result of 145,309 rows selected
in MS-Access, with a table Linked to the SQL Server table, I get the
result of 143,516.


Does anyone have any idea why this might happen?

Many thanks in advance for your kind consideration
Pat Backowski
I assume you have verified that the same data exists in both places ...
If so, here is my theory:

Dates are stored differently in each system, resulting in different resolution. Two things:
1. you need to use a less ambiguous date format especially if you plan on deploying this to systems where the regional settings might be different, and
2. more importantly, you need to make this more "bullet-proof" in order to use it in both. I think this will work:

where trandate >= #2008-06-20# and
trandate < #2008-06-21#

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Update string too long?
    ... > And we won't question to reason for this routine as you asked... ... Pro SQL Server 2000 Database Design - ... > support the full length of the inserted data? ...
    (microsoft.public.sqlserver.programming)
  • RE: Identity/Seed Values
    ... is not a substitute for this. ... SQL Server doesn't change the IDENTITY value ... IDENTITY yourself except by deleting and then inserting a row (another reason ... why you shouldn't tie external meaning to an arbitrary IDENTITY value). ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005 Password Expiration
    ... New error codes ... Reason: Password change failed. ... password does not meet policy requirements because it is too short. ... Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Different results in SQL and Access
    ... will fail for a row with a trandate of 2008-06-20 11:30 AM, ... The Date function stores the date as 12:00:00 AM. ... with the whole number representing the ... SQL Server stores datetimes as paired integers, ...
    (microsoft.public.access.queries)
  • Re: Migrating from 97 to 2002+ / JET to MSDE
    ... On the other hand with such system now that includes emailing of data ... If you move to sql server, then it seems reasonable to have each of the ... I see no reason to migrate to sql server unless you going to use the extra ... And, as mentioned, ms-access is not going ...
    (microsoft.public.access.forms)