ODBC date range with Providex problem

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
jeff7880Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
leclairmConnect With a Mentor Commented:
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
 
leclairmCommented:
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
 
jeff7880Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
leonstrykerCommented:
>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
 
jeff7880Author Commented:
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
 
jeff7880Author Commented:
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
All Courses

From novice to tech pro — start learning today.