Re: VBA ADO SQL Query Problem

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" <KDales@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ABC9BBD3-413F-415F-8359-A68DE5E812D2@xxxxxxxxxxxxxxxx
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 :confused:


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30634
View this thread:
http://www.excelforum.com/showthread.php?threadid=529937




.

Relevant Pages

  • RE: Excel & MS Query caused compile errors
    ... My data is in an Excel file called ... The format of it were distorted after copying and pasting. ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ...
    (microsoft.public.excel.programming)
  • Re: Prepare, SQL query with to_date call
    ... SQL query with to_date call ... The Oracle ODBC driver has a problem with date parameters although I'm ... using a date format containing the RR format. ...
    (perl.dbi.users)
  • Re: No one could logon to productio database for a while
    ... Puget Sound Oracle Users Group ... code for script is quite simple, it does a v$sql to find all sql ... The script ask for the hash value of the SQL statement. ... col "SLAVE SQL" format A95 WORD_WRAP ...
    (comp.databases.oracle.server)
  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: mySQL Problem
    ... And when the query gets executed i get back the following error: ... Actually, problem is proper quoting, not the format or anything else. ... the SQL standard and won't work on any other RDBMS I'm familiar with. ... you admit that mssql uses something DIFFERENT and PARTICULAR to alias? ...
    (comp.lang.php)
Loading