ODBC date range with Providex problem

Posted on 2006-07-12
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
Question by:jeff7880
  • 3
  • 2
LVL 11

Expert Comment

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 & "'"


Author Comment

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.
LVL 29

Expert Comment

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.

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.


Author Comment

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.
LVL 11

Accepted Solution

leclairm earned 500 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...

Author Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…

776 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