Access to SQL-Server Date/Time field problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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



Lucas Kartawidjaja said:
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

Christine King said:
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.
Click to expand...
Click to expand...
 
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

Christine King said:
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



Lucas Kartawidjaja said:
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

Christine King said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top