?
Solved

ODBC date range with Providex problem

Posted on 2006-07-12
6
Medium Priority
?
1,961 Views
Last Modified: 2013-12-25
I'm hitting a MAS90 database using the Providex driver using an ADODB connection. Assuming the connection is set up right does anyone see any problems with it? Here's a code sample.

conn.Open "DSN=SOTAMAS90_USR;UID=" & AliasUser & ";PWD=" & AliasPassword & ";Company=" & Company
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
strSqlStatement = "select distinct SalesOrderNumber, ShipExpireDate, BillToName from SO1_SOEntryHeader WHERE ShipExpireDate BETWEEN #" & DTPicker1.Value & "# AND #" & DTPicker2.Value & "#"
rs.Open strSqlStatement, conn, adOpenForwardOnly, adLockReadOnly

On the open I get the error:

Error -2147217900 - [ProvideX][ODBC Driver]Expected lexical element not found: AND
0
Comment
Question by:jeff7880
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:leclairm
ID: 17093715
Have you tried using an single quote instead of #

strSqlStatement = "select distinct SalesOrderNumber, ShipExpireDate, BillToName from SO1_SOEntryHeader WHERE ShipExpireDate BETWEEN '" & DTPicker1.Value & "' AND '" & DTPicker2.Value & "'"


0
 

Author Comment

by:jeff7880
ID: 17094217
Tried using single quotes and got the error:

Error -2147467259 - [ProvideX][ODBC Driver]Invalid operand for operator: BETWEEN


In addition we used EXCEL with the query and it returned results without any error, if that helps narrow anything down. I also tried using the dates with >= and <= with the similar results.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17095959
>Dim rs As New ADODB.Recordset
> Set rs = New ADODB.Recordset

You should not use this dim with this set. This is the correct format

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

>I also tried using the dates with >= and <= with the similar results.

Could you show this SQL string.  Your driver may not recognize the BETWEEN syntax.

Leon
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jeff7880
ID: 17100101
Ok guys, I figured this out already but I will award the points if someone else does. Heres a hint.. For whatever reason the format of the query had to be changed so that the between was a sub query and also the dates had to be formatted yyyy-mm-dd.
0
 
LVL 11

Accepted Solution

by:
leclairm earned 2000 total points
ID: 17100140
I saw in the following doc that the format of the date needed to be changed.  Not sure why you'd need a subquery though...


http://www.pvx.com/downloads/documentation/manuals/PVX-ODBC3x.pdf
0
 

Author Comment

by:jeff7880
ID: 17100488
This format worked.

SELECT DISTINCT SO1_SOEntryHeader.SalesOrderNumber, SO1_SOEntryHeader.ShipExpireDate, SO1_SOEntryHeader.BillToName FROM SO1_SOEntryHeader SO1_SOEntryHeader WHERE (SO1_SOEntryHeader.ShipExpireDate Between {d '1998-07-12'} And {d '2006-07-12'})

Another interesting thing is that I plugged the original query into EXCEL and hit the ODBC connection with the original query, but failed to notice that it changed the query to the format shown here. So I would suggest that if anyone has a formatting problem with a query they might want to try it in EXCEL or something to see how it changes the format.

I'm splitting the points
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question