Access to SQL-Server Date/Time field problem

[Guest]

I've got Access2002 with SQL-server tables linked in. The field is appearing
as date/time in Access, but if I put #06:45:00#, say as a criteria, or use
the [filter on] button, I get no data at all. Any ideas? The datefield is
datetime (8) in SQL-Server.

 

[Guest]

Hi there

Just wondering, it seemed that you might be missing the date? Depending on
your query, I guess, try something with date in it such as #6/8/2006
06:45:00# or #6/8/2006#.

Lucas

 

[Guest]

thanks for replying. It's purely a field that holds a time - the time a bus
departs in fact. So it doesn't have a date because every day for ever has a
0645 departure. If it's putting a date in itself I can't see it.
--
Cheers,
Christine King

Hi there

Just wondering, it seemed that you might be missing the date? Depending on
your query, I guess, try something with date in it such as #6/8/2006
06:45:00# or #6/8/2006#.

Lucas

I've got Access2002 with SQL-server tables linked in. The field is appearing
as date/time in Access, but if I put #06:45:00#, say as a criteria, or use
the [filter on] button, I get no data at all. Any ideas? The datefield is
datetime (8) in SQL-Server.

 

[Guest]

Oh ok. In that case, do you by any chance have access to query the SQL Server
table directly using Query Analyzer? If it is possible, try to query the
date-time field on SQL Server. I suspect that when you just enter the time
information into SQL Server, SQL Server will append the date automatically.
The date that SQL Server will append is 1/1/1900.

If my guess is correct, you should be able to run the query by using the
following:
#1/1/1900 06:45:00#

Lucas

thanks for replying. It's purely a field that holds a time - the time a bus
departs in fact. So it doesn't have a date because every day for ever has a
0645 departure. If it's putting a date in itself I can't see it.
--
Cheers,
Christine King

Hi there

Just wondering, it seemed that you might be missing the date? Depending on
your query, I guess, try something with date in it such as #6/8/2006
06:45:00# or #6/8/2006#.

Lucas

I've got Access2002 with SQL-server tables linked in. The field is appearing
as date/time in Access, but if I put #06:45:00#, say as a criteria, or use
the [filter on] button, I get no data at all. Any ideas? The datefield is
datetime (8) in SQL-Server.