Is [Date] in the database a text field, a date field, or a date/time field?

One of the problems in ADO/ODBC is the fact that by using methods that have
to apply to any potential data source they have their own ways of handling
data types and conversion. I suspect that when you use the LIKE query, ODBC
must be converting the dates on both ends to text (LIKE only applies to text
fields) and because it is converting both itself it naturally uses the same
format. When you try to convert it yourself somehow it is not matching the
format; that is why the type of field matters - it would affect the format
that ODBC would choose in trying to convert the field to text.

When the exact syntax of the SQL is hard to figure out, here is a "trick" I
use: I build a query in MSQuery that mimics the query I want to build
through ADO, then I look at the syntax by viewing the SQL inside MSQuery. I
did a test using an Access database that has some date/time fields and this
was the result:

WHERE (`TABLENAME`.DateTimeField Between {ts '2005-01-01 00:00:00'} And {ts
'2005-08-01 00:00:00'})

With that in mind try this for your query:
WHERE [" & TableName & "].[Date] = {ts '" & Format(sDate,"yyyy-mm-dd
hh:nn:ss & "'}", cn, , , adCmdText

But if that does not work try my MSQuery trick.
--
- K Dales


"gti_jobert" wrote:


Also....when I open up my Db table the [Date] Col is displayed in format
'24/03/2006'

Dont have a clue what I'm doing wrong


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=529937