E_Fail Status - I'm STUMPED

[jaycast]

Ok, I've searched all over for an answer to fixing this dreaded E_Fail Status error I receive when trying to run a VB report against a SQL 7.0 Data Source. The report ran fine the last time I tried, and nothing has changed in the procedure. Can anyone look my code over and tell me where I could possibly be going wrong??


Public sql As New adodb.Connection
Private Sub Command1_Click()
Dim x As Integer, count As Integer, count2 As Integer, SQLP As String, SQLP2 As String, SQLS As String, rst As New adodb.Recordset, mysql As String
Dim rst2 As New adodb.Recordset, fldloop As adodb.Field, Crst As New adodb.Recordset, DateMin As Date, datmax As Date, rstDate As New adodb.Recordset
Dim ACommand As adodb.Command

Dim test As String

sql.Provider = "MSDataShape"
sql.ConnectionTimeout = 100
sql.CommandTimeout = 100
sql.Open "DSN=FreightInvoices", "sa", ""



rst.Open "Select * From tblCustomerstoSupress", sql, adOpenKeyset, adLockReadOnly


count = rst.RecordCount
x = 0


For x = 1 To count
SQLS = SQLS & "(tblFedExTrackNum.SoldTo LIKE " & "'" & rst![soldto] & "'" & &quot"
If x < count Then
SQLS = SQLS & &quot; And Not &quot;
Else: SQLS = SQLS & &quot&quot;
End If
rst.MoveNext
Next x


SQLP2 = &quot;APPEND({SELECT qryTrackingNumberCharges.TrackingNum, tbl_110_Detail_RatesAndCharges.ChargeCode, &quot; & _
&quot;tblFedExSpecialChargeCodes.Description, tbl_110_Detail_RatesAndCharges.ChargeAmount &quot; & _
&quot;FROM qryTrackingNumberCharges &quot; & _
&quot;LEFT OUTER JOIN tbl_110_Detail_RatesAndCharges ON qryTrackingNumberCharges.InvoiceNumber = tbl_110_Detail_RatesAndCharges.InvoiceNumber &quot; & _
&quot;AND qryTrackingNumberCharges.LXID = tbl_110_Detail_RatesAndCharges.LXID &quot; & _
&quot;LEFT OUTER JOIN tblFedExSpecialChargeCodes ON tbl_110_Detail_RatesAndCharges.ChargeCode = tblFedExSpecialChargeCodes.CodeValue &quot; & _
&quot;GROUP BY qryTrackingNumberCharges.TrackingNum, tbl_110_Detail_RatesAndCharges.ChargeCode, tblFedExSpecialChargeCodes.Description, &quot; & _
&quot;tbl_110_Detail_RatesAndCharges.ChargeAmount &quot; & _
&quot;ORDER BY qryTrackingNumberCharges.TrackingNum, tbl_110_Detail_RatesAndCharges.ChargeCode} RELATE TrackingNum TO TrackingNum)&quot;

SQLP = &quot;SHAPE{SELECT qryInvoiceFreightSums.InvoiceNum, qryInvoiceFreightSums.FedExFreight, tblFedExTrackNum.SumOffreight_xinvbox AS MunicsFreight, &quot; & _
&quot;qryInvoiceFreightSums.FedExFreight - tblFedExTrackNum.SumOffreight_xinvbox AS Difference, tblFedExTrackNum.shipdate_xinvbox, &quot; & _
&quot;tblFedExTrackNum.TrackingNum, tblFedExTrackNum.itemid_invdet, tblFedExTrackNum.SoldTo, tblNonPrePaidCust.[Customer ID] &quot; & _
&quot;FROM qryInvoiceFreightSums &quot; & _
&quot;INNER JOIN qryTrackingNumberCharges ON qryInvoiceFreightSums.TrackingNum = qryTrackingNumberCharges.TrackingNum &quot; & _
&quot;AND qryInvoiceFreightSums.FedExFreight = qryTrackingNumberCharges.ChargeSum &quot; & _
&quot;Inner Join tblFedExTrackNum ON qryInvoiceFreightSums.TrackingNum = tblFedExTrackNum.TrackingNum &quot; & _
&quot;LEFT OUTER JOIN tblNonPrePaidCust ON tblFedExTrackNum.SoldTo = tblNonPrePaidCust.[Customer ID] &quot; & _
&quot;GROUP BY qryInvoiceFreightSums.InvoiceNum, qryInvoiceFreightSums.FedExFreight, tblFedExTrackNum.SumOffreight_xinvbox, &quot; & _
&quot;qryInvoiceFreightSums.FedExFreight - tblFedExTrackNum.SumOffreight_xinvbox, tblFedExTrackNum.shipdate_xinvbox, &quot; & _
&quot;tblFedExTrackNum.TrackingNum, tblFedExTrackNum.itemid_invdet, tblFedExTrackNum.SoldTo, tblNonPrePaidCust.[Customer ID] &quot; & _
&quot;HAVING (qryInvoiceFreightSums.FedExFreight - tblFedExTrackNum.SumOffreight_xinvbox > 0) &quot; & _
&quot;AND (NOT &quot; & SQLS & _
&quot;AND (tblNonPrePaidCust.[Customer ID] Is Null) &quot; & _
&quot;ORDER BY tblFedExTrackNum.SoldTo, qryInvoiceFreightSums.InvoiceNum} AS ParentRS &quot; & SQLP2

rst2.CursorLocation = adUseServer
rst2.Open SQLP, sql, adOpenKeyset, adLockReadOnly
rst2.ActiveConnection = Nothing


rstDate.Open &quot;SELECT MIN(shipdate_xinvbox) AS [Min], MAX(shipdate_xinvbox) AS [Max] From tblFedExTrackNum&quot;, sql, adOpenKeyset, adLockReadOnly


DateMin = rstDate![Min]
DateMax = rstDate![Max]

Set DR.DataSource = rst2

With DR.Sections(&quot;Section2&quot
.Controls(&quot;lblDateRange&quot.Caption = &quot;For Ship Date Range &quot; & CStr(DateMin) & &quot; through &quot; & CStr(DateMax) & &quot; .&quot;
End With

With DR.Sections(&quot;Section6&quot
.Controls(&quot;txtInvoiceNum&quot.DataField = &quot;InvoiceNum&quot;
.Controls(&quot;txtFedExFreight&quot.DataField = &quot;FedExFreight&quot;
.Controls(&quot;txtMunicsFreight&quot.DataField = &quot;MunicsFreight&quot;
.Controls(&quot;txtDifference&quot.DataField = &quot;Difference&quot;
.Controls(&quot;txtTrackingNum&quot.DataField = &quot;TrackingNum&quot;
.Controls(&quot;txtitemid_invdet&quot.DataField = &quot;itemid_invdet&quot;
.Controls(&quot;txtSoldTo&quot.DataField = &quot;SoldTo&quot;
.Controls(&quot;txtshipdate_xinvbox&quot.DataField = &quot;shipdate_xinvbox&quot;


End With
With DR.Sections(&quot;Section1&quot
.Controls(&quot;txtChargeCode&quot.DataMember = &quot;Chapter1&quot;
.Controls(&quot;txtChargeCode&quot.DataField = &quot;ChargeCode&quot;
.Controls(&quot;txtDescription&quot.DataMember = &quot;Chapter1&quot;
.Controls(&quot;txtDescription&quot.DataField = &quot;Description&quot;
.Controls(&quot;txtChargeAmount&quot.DataMember = &quot;Chapter1&quot;
.Controls(&quot;txtChargeAmount&quot.DataField = &quot;ChargeAmount&quot;
End With
With DR
.LeftMargin = (1440 * 0.25)
.RightMargin = (1440 * 0.25)
.TopMargin = 0
.BottomMargin = 0

End With
DR.Show

End Sub

Thanks in advance

 

[ca8msm]

Could you let us know where you are getting the error?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[jaycast]

Of course. Sorry about that...was in a hurry.

I'm getting the error upon opening the rst2 recordset.

rst2.CursorLocation = adUseServer
rst2.Open SQLP, sql, adOpenKeyset, adLockReadOnly
rst2.ActiveConnection = Nothing

 

[jaycast]

Bump

 

[johnwm]

I read somewhere (but can't recall where) that each SQL flavour has a maximum length for a SQL statement. Might be worth doing a breakpoint on the first statement after you've finished building the big SQL query, and do a ?len(SQLP) in the immediate window. It looks like it's length will vary depending on the variables that you're including, so if the length looks close to 2048 (a significant number) you might try re-writing the query using aliases to shorten it

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jaycast]

I tried shortening my SQL string to under 1000 characters and I still receive the same error