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
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
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.
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
> 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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This format worked.
SELECT DISTINCT SO1_SOEntryHeader.SalesOrd erNumber, SO1_SOEntryHeader.ShipExpi reDate, SO1_SOEntryHeader.BillToNa me FROM SO1_SOEntryHeader SO1_SOEntryHeader WHERE (SO1_SOEntryHeader.ShipExp ireDate 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
SELECT DISTINCT SO1_SOEntryHeader.SalesOrd
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
strSqlStatement = "select distinct SalesOrderNumber, ShipExpireDate, BillToName from SO1_SOEntryHeader WHERE ShipExpireDate BETWEEN '" & DTPicker1.Value & "' AND '" & DTPicker2.Value & "'"