Access linked to SQL Server - 2 day offset error

We recently translated the backend db from Access(97) to SQL Server.
We are still using Access frontends. I have an update query in the
Access front end that uses a lookup table to populate fields. The
common fields between the table and the lookup table are the primary
key (LocID) and date & time fields. The query is:

UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
tblPT.Salinity = [tblPT_Offsets].[Salinity]
WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
[EndDate]));

This worked fine in Access and seemed to work fine after switching to
Access, but on closer look, there is exactly a 2 day error being
introduced. A quick search of the newsgroups brings up lots of Access
to SQL date problems, but a 2 day offset seems rather strange? Any
ideas??

I know the field names Date and Time are inappropriate, but legacy
issues are a pain in the butt to resolve!! Could this be a problem?

Davidarchean1@.yahoo.com (David) wrote in message news:<31e424c8.0405251702.14950a8f@.posting.google.com>...
> We recently translated the backend db from Access(97) to SQL Server.
> We are still using Access frontends. I have an update query in the
> Access front end that uses a lookup table to populate fields. The
> common fields between the table and the lookup table are the primary
> key (LocID) and date & time fields. The query is:
> UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
> tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
> tblPT.Salinity = [tblPT_Offsets].[Salinity]
> WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
> ((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
> [EndDate]));
> This worked fine in Access and seemed to work fine after switching to
> Access, but on closer look, there is exactly a 2 day error being
> introduced. A quick search of the newsgroups brings up lots of Access
> to SQL date problems, but a 2 day offset seems rather strange? Any
> ideas??
> I know the field names Date and Time are inappropriate, but legacy
> issues are a pain in the butt to resolve!! Could this be a problem?
> David

Can you post some sample data to show the problem? It's not really
clear from the details above what you're seeing. Are you using the
query above with linked tables in Access?

Simon|||On 25 May 2004 18:02:25 -0700, David wrote:

>We recently translated the backend db from Access(97) to SQL Server.
>We are still using Access frontends. I have an update query in the
>Access front end that uses a lookup table to populate fields. The
>common fields between the table and the lookup table are the primary
>key (LocID) and date & time fields. The query is:
>UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
>tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
>tblPT.Salinity = [tblPT_Offsets].[Salinity]
>WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
>((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
>[EndDate]));
>This worked fine in Access and seemed to work fine after switching to
>Access, but on closer look, there is exactly a 2 day error being
>introduced. A quick search of the newsgroups brings up lots of Access
>to SQL date problems, but a 2 day offset seems rather strange? Any
>ideas??
>I know the field names Date and Time are inappropriate, but legacy
>issues are a pain in the butt to resolve!! Could this be a problem?
>David

Hi David,

I don't know if there is any relation at all, but just yesterday I
answered a question in another newsgroup about a 2-day difference being
introduced when copying data between SQL Server and Excel. That poster
seemed to use the internal date representation instead of formatted dates.
Your query does contain code to format the date, so it should not cause
this effect - but the fact your time difference is 2 days as well does
strike me as funny.

In case you want to check it out, follow the link below.

http://www.google.com/groups?q=exce...04ax.com&rnum=1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funnily enough, I did fix my problem with a simple fix, but I'm glad
to understand the problem. (Simon, the queries are in Access
frontends and the tables are linked via ODBC to a SQL Server.)

The sql code I posted (from the access frontend) actually already had
the fix. I had already changed

([Date] + [Time])

to ((Format([Date]+[Time],"mm/dd/yy hh:nn"))

and this fixed the problem. After reading the replies (Hugo, you hit
it on the head), it seems that passing SQL Server dates formatted both
as dates and double and then having SQL Server do date lookups, etc.
is dangerous. But by making sure all dates are passed as dates, I'm
assuming the ODBC translation takes care of the differences in the
date numbering scheme of Access/SQL. I think that sums it up?

thanks!
David