VBA ADO SQL Query Problem

[gti_jobert]

Hi all,

Long winded title for this one!

Got another problem with my SQL Query after connecting from VBA Excel
via ADO to MS Access. I'm hoping someone in here might have an
incling...


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

.Open "SELECT [Date], [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [" & TableName & "].[Date] LIKE '" & sDate & "'", cn, , , adCmdText

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


sDate is declared as a date and the above statement performs as it
should, but i'm getting an error 'Data Type Mismatch in Criteria
Expression' when I change my statement from LIKE to =, example;


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

.Open "SELECT [Date], [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [" & TableName & "].[Date] = '" & sDate & "'", cn, , , adCmdText

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


I need to be able to perform a BETWEEN statement on the date and this
does not work either producing the same error message. Anyone have any
ideas why this is happening?

TIA....Any Ideas Appeciated!

 

[Jean-Yves]

Hi,

To avoid any problem, always format your date(s) to avoid problem

"SELECT [Date], [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE ( END_DATE BETWEEN '" & Format(sDate, "dd-mmm-yyyy") & "' AND '"
& Format(DateAdd("m", 2, sDate), "dd-mmm-yyyy") & "' ) , cn, , , adCmdText
I did take the table name out from the second line. the date range is date 1
and date 1 + 2 months

HTH
Regards
Jean-Yves

 

[gti_jobert]

Hi thanks for your reply,

I have tried your formatting of my sDate and eDate but it still give me
the error; I did a printout of the SQL query to view the variables as
shown below;

SQL:

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

'SQL select data
.Open "SELECT [Date], [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [Date] = '" & Format(sDate, "dd/mm/yyyy") & "'", cn, , , adCmdText

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


Echo SQL:
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing]
WHERE [Date] = '28/03/2006'

Perhaps I should be formatting the [Date] column in the Db - but how
would I go about this?

TIA

 

[gti_jobert]

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

Dont have a clue what I'm doing wrong :confused

 

[Jean-Yves]

Hi again,

Even when copy and paste I some time have problems
a simple space missing or quote not position correctly and there you go.
Try this,
Run you query bit by biit and see at which stage you have a problem
Select A FROM ..
Select A, B FROM
Select A FROM WHERE ...

By the way, are you really using ADO, or DAO
In the excell DB query, the SQL string length is limited to a certain
maximum of characters.
The solution is then to convert the SQL string in an array.

Regards

JY

 

[Guest]

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.

 

[Jean-Yves]

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

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

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?action=getinfo&userid=30634
View this thread:
http://www.excelforum.com/showthread.php?threadid=529937

 

[Jean-Yves]

HI,

Can you givce more lines of code how you open your connection, etc, the
library you are using ?
JY

 

[gti_jobert]

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: