Solving "Invalid Procedure Call" error on running query 3

[Laura776]

Hi,

I've got a fairly complex query that has been running quite happily in Access 2000. I made some changes to the queries in Access 2000 and they worked fine.
The corporate standard has now upgraded to Access 2003. The database still works, but one specific query keeps throwing up a "Invalid Procedure Call" error.
All other queries seem to work fine, and the data used to run this query (populated from a text import) seems to be OK.

I can't seem to pin down what this error refers to... is it Access version related? data related?...

Any suggestions on what this error means, and possible solutions?

Many thanks!
Laura...

 

[rjoubert]

Can you post the SQL behind your query?

 

[Laura776]

Hi! thanks for your help, the SQL is very lengthy - but here goes. As I said, it worked fine for sometime. When viewed in design view, it's not so difficult to follow:

SELECT DISTINCT [Comet Header & Footer Query].[Header 1], Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] AS ClaimersReferenceNumber, [RecordType] & [VersionNumber] & [BatchNumber] & [ClaimerIdentification] & [ClaimersReferenceNumber] & [KindOfClaim] & [GuaranteeCode] & [Daterepairreceivedfromconsumer] & [DateRepairCompleted] & [Workshop/Field] & [DateRepairReceivedbytheRepairer] & [StockRepairIndicator] & [ConsumerName] & [ConsumerAddress] & [ConsumerAddress2] & [ConsumerPhone] AS [Prod Info1], [ConsumerPostalCode] & [ConsumerTown] & [ConsumerCountrycode] & [AuthorisationNumber] & [BrandName] & [ProductionNumber] & [ModelNumber] & [DateofPurchase] AS [Prod Info2], [ExtendedGuaranteeNumber] & [SellingDealerName] & [SellingDealerAddress] & [SellingDealerAddress2] & [SellingDealerPostalCode] & [SellingDealerTown] & [SellingDealerNumber] & [SellingDealerCountry] AS [Prod Info3], Len([prod info1]) AS Expr3, [Comet Multiply Line Query].[Detail 1], IIf(Len([Fault code])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & IIf(Len([Part No 1])=16,Mid(([Part No 1]),5,12),Mid([Blanks],2,12)) & Mid([Blanks],2,38) & [qty1],"") AS [Repair Detail], IIf(Len([Part No 2])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & Mid([Part No 2],5,12) & Mid([Blanks],2,38) & IIf(Len([Expr1036])=2,[Expr1036],[Expr1036] & Mid([Blanks],2,1)),"") AS [Repair Detail2], IIf(Len([Part No 3])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & Mid([Part No 3],5,12) & Mid([Blanks],2,38) & IIf(Len([Field39])=2,[Field39],[Field39] & Mid([Blanks],2,1)),"") AS [Repair Detail3], IIf(Len([Part no 4])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & Mid([Part no 4],5,12) & Mid([Blanks],2,38) & [Field43] & Mid([Blanks],2,1),"") AS [Repair Detail4], IIf(Len([part no 5])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & Mid([Part no 5],5,12) & Mid([Blanks],2,38) & IIf(Len([Field47])=2,[Field47],[Field47] & Mid([Blanks],2,1)),"") AS [Repair Detail5], IIf(Len([Part no 6])>1,"0210CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & [Loc Code] & IIf(Len([Fault code])=4,[Fault code]," ") & " " & [Def Code] & [Rep Code] & [Sec Code] & " " & Mid([Part no 6],5,12) & Mid([Blanks],2,38) & IIf(Len([Field51])=2,[Field51],[Field51] & Mid([Blanks],2,1)),"") AS [Repair Detail6], "0510CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & "00F" & Mid([Fault Description],1,70) AS [Additional Info], IIf(Len([TextLineMaster])>1,("0510CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & "01R" & Mid([TextLineMaster],1,70)),"") AS [Additional Info2], IIf(Len([TextLineMaster])>70,("0510CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & "02R" & Mid([TextLineMaster],71,70)),"") AS [Additional Info3], IIf(Len([TextLineMaster])>140,("0510CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & "03R" & Mid([TextLineMaster],141,70)),"") AS [Additional Info4], IIf(Len([TextLineMaster])>210,("0510CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) & "2143048 " & Mid([Zeros],1,(15-Len([Job No]))) & +[Job No] & "04R" & Mid([TextLineMaster],211,70)),"") AS [Additional Info5], "01" AS RecordType, "10" AS VersionNumber, "CT" & +Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now())) AS BatchNumber, "2143048 " AS ClaimerIdentification, "R" AS KindOfClaim, " " AS GuaranteeCode, [Comet Multiply Line Query]![initial repair date] AS Daterepairreceivedfromconsumer, [Comet Multiply Line Query]![completed repair date] AS DateRepairCompleted, IIf([Comet Multiply Line Query]![Repair type]="W","W","F") AS [Workshop/Field], [Comet Multiply Line Query]![initial repair date] AS DateRepairReceivedbytheRepairer, IIf(Len([Comet Multiply Line Query]![Stock Repair Indicator])=1,[Comet Multiply Line Query]![Stock Repair Indicator]," ") AS StockRepairIndicator, IIf([Local Field 1] Is Not Null,Left([Local Field 1] & String(35," "),35),IIf([Local Field 1] Is Null,String(35,"."),String(35,"."))) AS ConsumerName, IIf([Local Field 2] Is Not Null,Left([Local Field 2] & String(35," "),35),IIf([Local Field 2] Is Null,String(35,"."),String(35,"."))) AS ConsumerAddress, IIf([Local Field 3] Is Not Null,Left([Local Field 3] & String(35," "),35),IIf([Local Field 3] Is Null,String(35,"."),String(35,"."))) AS ConsumerAddress2, "000000000000000" AS ConsumerPhone, IIf([Local Field 6] Is Not Null,Left([Local Field 6] & String(9," "),9),IIf([Local Field 6] Is Null,String(9,"."),String(9,"."))) AS ConsumerPostalCode, IIf([Local Field 4] Is Not Null,Left([Local Field 4] & String(35," "),35),IIf([Local Field 4] Is Null,String(35,"."),String(35,"."))) AS ConsumerTown, "GB " AS ConsumerCountrycode, " " AS AuthorisationNumber, " " AS BrandName, [SerailNo] & Mid([Blanks],2,(35-Len([SerailNo]))) AS ProductionNumber, [Comet Multiply Line Query]![retail model] & Mid([Blanks],2,(35-Len([Comet Multiply Line Query]![retail model]))) AS ModelNumber, [Comet Multiply Line Query]![Purchase date] AS DateofPurchase, " " AS ExtendedGuaranteeNumber, " " AS SellingDealerName, " " AS SellingDealerAddress, " " AS SellingDealerAddress2, " " AS SellingDealerPostalCode, " " AS SellingDealerTown, "2143048 " AS SellingDealerNumber, "GB " AS SellingDealerCountry, [ClaimersReferenceNumber] & [Comet Multiply Line Query]![Detail 1] AS Expr4, (IIf(Not (IsNull([Comet Multiply Line Query]![Note line 1])),[Comet Multiply Line Query]![Note line 1])) & (IIf(Not (IsNull([Comet Multiply Line Query]![Note line 2]))," " & [Comet Multiply Line Query]![Note line 2])) & (IIf(Not (IsNull([Comet Multiply Line Query]![Note line 3]))," " & [Comet Multiply Line Query]![Note line 3])) & (IIf(Not (IsNull([Comet Multiply Line Query]![Note line 4]))," " & [Comet Multiply Line Query]![Note line 4])) AS TextLineMaster, IIf([Position Number]="0"," ",(Mid([Position Number],1,8) & Mid([Blanks and Zeros]!Blanks,2,9-Len([Position Number])))) AS [Loc Code], IIf(Len([Defect Code])=0," ",Mid([Defect Code],1,4) & (Mid([Blanks],2,2-Len([Defect Code])))) AS [Def Code], IIf(Len([Repair Code])=0," ",Mid([Repair Code],1,4) & (Mid([Blanks],2,2-Len([Repair Code])))) AS [Rep Code], IIf(Len([Section Code])=1," ",Mid([Section Code],1,3) & Mid([Blanks],1,3-Len([Section Code]))) AS [Sec Code], IIf([qty]=0," ",(IIf(Len([qty])=1,("0" & [qty]),[qty]))) AS Qty1
FROM [Blanks and Zeros], [Comet Header & Footer Query], [Comet Multiply Line Query]
ORDER BY Mid([Zeros],1,(15-Len([Job No]))) & +[Job No], [Comet Multiply Line Query].[Detail 1];

 

[lespaul]

Since you are apparently using two other queries within this one, you will also need to check and make sure they still run independently; the error could be coming from one of them.

[Comet Header & Footer Query]
[Comet Multiply Line Query]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

This

Mid([Zeros],1,(15-Len([Job No])))

will generate an invalid procedure call if Len([Job No]) is greater than 15.

As will this one

Mid([Blanks],2,(35-Len([Comet Multiply Line Query].[retail model])))

if [Comet Multiply Line Query].[retail model] is more than 35 characters.

You can also replace

+Year(Now()) & IIf(Len(Month(Now()))=1,"0" & Month(Now()),Month(Now())) & IIf(Len(Day(Now()))=1,"0" & Day(Now()),Day(Now()))

that appears in several places with

Format(Date,"yyyymmdd")

You can replace

IIf([Local Field 1] Is Not Null,Left([Local Field 1] & String(35," "),35),IIf([Local Field 1] Is Null,String(35,"."),String(35,"."))) AS ConsumerName

with

IIf([Local Field 1] Is Not Null,Left([Local Field 1] & String(35," "),35),String(35,".")) AS ConsumerName

Ans similar comments apply to the following fields.

 

[Laura776]

Golum,

FYI solved the problem; thanks for the improved code! Error was arising in:

IIf(Len([Section Code])=1," ",Mid([Section Code],1,3) & Mid([Blanks],1,3-Len([Section Code]))) AS [Sec Code]

Section code on the input data contained a value "#" on one particular record which gave the error (typo on data entry).

Is there a catch all error code I can put into the query to give a more user friendly error message, for these type of data-related error messages?

Many thanks.

 

[Golom]

Queries don't have any error trapping mechanism. Error trapping (if such there be) is in the code that runs the query. If the query is being run from the query designer then Access code is being processed and it will report the standard error messages like the ones that you are seeing.

If you have your own VB code written that runs the query then you can do your own error trapping something like this.

Private Sub Command5_Click()
    Dim db                          As DAO.Database
    Dim rs                          As DAO.Recordset
    Dim qdf                         As DAO.QueryDef
    Set db = DAO.DBEngine(0).OpenDatabase(".\chapsmast.mdb")
    On Error GoTo ErrorExit
    Set qdf = db.QueryDefs("ErrorQuery")
    Set rs = qdf.OpenRecordset
    Debug.Print rs.Fields(0).Value
    Exit Sub
    
ErrorExit:
    If Err.Number = 5 Then      [COLOR=black cyan]' Invalid Procedure Call[/color]
        MsgBox "Bad lengths in some database fields" & vbCrLf & _
               "Now is the time to PANIC!!!", vbExclamation, _
               "HELP! HELP! HELP!"
    Else
        MsgBox Err.Number & " - " & Err.Description
    End If
End Sub

 

[lespaul]

Is that a message box from an actual program?

les

 

[Golom]

Leslie

No ... but there are times when I'm tempted ...