HI,

For long queries on a Oracle DB, I often meke it easy by using Business
Object where I copy the SQL statement from.
Well, this did not work for ADO with date fields. I had to format my date
query parameter in the Oracle date format.
But the query DID work, only no records were retrieved (no match)
The error comes from something else
JY

"K Dales" wrote in message
...
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

Hello all,

Thankyou for your input! The problem is that when you perform an '=' or
BETWEEN operation the criteria has to be wrapped in ##.


Code:
--------------------

.Open "SELECT [Date], SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & Format(sDate, dd / mm / yyyy) & "# AND #" & eDate & "# " & _
"GROUP BY [Date] ORDER BY [Date]", cn, , , adCmdText

--------------------


The above now works, again thanks all for input!


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