Solved

Getting Invalid Procedure Call When Running My Query

Posted on 2013-06-20
5
673 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 300 total points
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

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

Thanks,
gdunn59
0
 

Author Comment

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

Thanks,
gdunn59
Names.xlsx
0
 

Author Comment

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

Thanks,
gdunn59
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now