Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

Getting Invalid Procedure Call When Running My Query

I have a query that has some InStr statements.  When I run the query, I am getting "Invalid Procedure Call".

Below are the different statements that I have in the query that I believe is causing the Invalid Procedure Call error, but I don't know why I'm getting the error:

FirstEmpName: IIf(InStrRev([tblEmployee_Audits].[Employee]," ")=InStr([tblEmployee_Audits].[Employee],",")+1,Mid([tblEmployee_Audits].[Employee],InStr([tblEmployee_Audits].[Employee],",")+2),Mid([tblEmployee_Audits].[Employee],InStr([tblEmployee_Audits].[Employee],",")+2,InStrRev([tblEmployee_Audits].[Employee]," ")-InStr([tblEmployee_Audits].[Employee],",")-2))

LastEmpName: Left([tblEmployee_Audits].[Employee],InStr([tblEmployee_Audits].[Employee],",")-1)

FirstMgrName: IIf(InStrRev([Manager_Name]," ")=InStr([Manager_Name],",")+1,Mid([Manager_Name],InStr([Manager_Name],",")+2),Mid([Manager_Name],InStr([Manager_Name],",")+2,InStrRev([Manager_Name]," ")-InStr([Manager_Name],",")-2))

LastMgrName: Left([Manager_Name],InStr([Manager_Name],",")-1)

FirstAuditorName: IIf(InStrRev([tblEmployee_Audits].[Auditor_Name]," ")=InStr([tblEmployee_Audits].[Auditor_Name],",")+1,Mid([tblEmployee_Audits].[Auditor_Name],InStr([tblEmployee_Audits].[Auditor_Name],",")+2),Mid([tblEmployee_Audits].[Auditor_Name],InStr([tblEmployee_Audits].[Auditor_Name],",")+2,InStrRev([tblEmployee_Audits].[Auditor_Name]," ")-InStr([tblEmployee_Audits].[Auditor_Name],",")-2))

LastAuditorName: Left([tblEmployee_Audits].[Auditor_Name],InStr([tblEmployee_Audits].[Auditor_Name],",")-1)

Open in new window

The Total Row all have Expression.

Help please!!!!!

Thanks,
gdunn59
0
gdunn59
Asked:
gdunn59
  • 3
1 Solution
 
peter57rCommented:
The problem will almost certainly be due to data values in your field that are not properly catered for in your expressions.  
I haven't looked at all the detail of your code, but have you tested null values, or values such as " ," or just "," or values that start or end with a comma or start with a space character or that have multiple spaces and/or commas.

I suspect what is happening is that your expression is producing an argument which has an invalid value, maybe a -ve value for instr()-1 or such expressions.
0
 
Dale FyeCommented:
Agree with Peter that it is likely that you have tried to use a left ( ) or Mid ( ) function with a negative number.

Can you provide some examples of what your Employee, Manager Name, and Auditor Name fields look like?  Provide dummy names.

Whenever I am going to try parse names, I generally try to create a function so that I can provide better code flow and processing than is available in a query expression.  It gives me the added benefit that I can document what I have done in the code so that someone maintaining my code or converting it years down the road can do so without too much difficulty.
0
 
gdunn59Author Commented:
Does it matter if some of the names have a middle initial, and others don't?

Thanks,
gdunn59
0
 
gdunn59Author Commented:
I've attached a spreadsheet with name examples for the Employee, Manager and Auditor.

Thanks,
gdunn59
Names.xlsx
0
 
gdunn59Author Commented:
I was getting the error because some records contained null values in the Employee, Manager or Auditor fields.

Thanks,
gdunn59
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now