Improve company productivity with a Business Account.Sign Up

x
?
Solved

Getting Invalid Procedure Call When Running My Query

Posted on 2013-06-20
5
Medium Priority
?
1,419 Views
Last Modified: 2013-06-27
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
Comment
Question by:gdunn59
  • 3
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1200 total points
ID: 39265293
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
 
LVL 50

Expert Comment

by:Dale Fye
ID: 39269713
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39271889
Does it matter if some of the names have a middle initial, and others don't?

Thanks,
gdunn59
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39271925
I've attached a spreadsheet with name examples for the Employee, Manager and Auditor.

Thanks,
gdunn59
Names.xlsx
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39280217
I was getting the error because some records contained null values in the Employee, Manager or Auditor fields.

Thanks,
gdunn59
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question