Prev: Operation must use an updateable query.
Next: Paste errors when copying column name in a group by function
From: Pat Backowski on 25 Jun 2008 05:45
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


From: Bob Barrows [MVP] on 25 Jun 2008 06:10
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"


From: BruceM on 25 Jun 2008 07:32
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(a)NOyahoo.SPAMcom> wrote in message
news:ujYiduq1IHA.1240(a)TK2MSFTNGP02.phx.gbl...
> 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"
>

From: Bob Barrows [MVP] on 25 Jun 2008 08:26
BruceM wrote:
> How about:
> trandate = #02/06/2008#

It sounds as if the field contains time as well as date. This criteria
will fail for a row with a trandate of 2008-06-20 11:30 AM, for example.
Of course, if the field contains no time component, then you are
correct.

> 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

Jet stores date/times as doubles, with the whole number representing the
number of days since the seed date and the decimal representing the time
of day.
SQL Server stores datetimes as paired integers, with the first integer
representing the number of days since the seed date (which is different
from the Jet seed date) and the second representing the number of
milliseconds since midnight - a consequence of this being that the
minimum resolution of time data is about .003 sec.

> (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).

He's talking about a linked table - his description was slightly "off".
Right-click in the Tables tab of the database window and select "Link
table". Change the file type to ODBC..., select a DSN pointing at a SQL
Server database, select a table, click OK, et voila, you have a table
linked to a table in SQL Server.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


From: BruceM on 25 Jun 2008 11:53
I misread 20 as 02 in the date, but even so I don't see how 11:30 AM would
fail when the specified time range is:
trandate >= #20/06/2008 00:00:01# and trandate <= #20/06/2008 23:59:59#
11:30 AM is between the first second of the day and the last second of the
day.
I wonder whether the date format (dd/mm/yyyy) is part of the problem. The
format mm/dd/yyyy is more typical as a regional setting. I still wonder
whether eliminating the first second of the day accounts for the difference.
Thanks for the explanation about linking a table to a table. I have to
admit I don't quite follow how that works, but I appreciate your taking the
time to write it down.


"Bob Barrows [MVP]" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:%233iCl6r1IHA.4848(a)TK2MSFTNGP05.phx.gbl...
> BruceM wrote:
>> How about:
>> trandate = #02/06/2008#
>
> It sounds as if the field contains time as well as date. This criteria
> will fail for a row with a trandate of 2008-06-20 11:30 AM, for example.
> Of course, if the field contains no time component, then you are
> correct.
>
>> 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
>
> Jet stores date/times as doubles, with the whole number representing the
> number of days since the seed date and the decimal representing the time
> of day.
> SQL Server stores datetimes as paired integers, with the first integer
> representing the number of days since the seed date (which is different
> from the Jet seed date) and the second representing the number of
> milliseconds since midnight - a consequence of this being that the
> minimum resolution of time data is about .003 sec.
>
>> (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).
>
> He's talking about a linked table - his description was slightly "off".
> Right-click in the Tables tab of the database window and select "Link
> table". Change the file type to ODBC..., select a DSN pointing at a SQL
> Server database, select a table, click OK, et voila, you have a table
> linked to a table in SQL Server.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

 |  Next  |  Last
Pages: 1 2 3 4
Prev: Operation must use an updateable query.
Next: Paste errors when copying column name in a group by function