Link to home
Start Free TrialLog in
Avatar of jeff7880
jeff7880

asked on

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
Avatar of leclairm
leclairm

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


Avatar of jeff7880

ASKER

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.
>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
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.
ASKER CERTIFIED SOLUTION
Avatar of leclairm
leclairm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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