Getting Invalid Procedure Call When Running My Query

Posted on 2013-06-20
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!!!!!

Question by:gdunn59
  • 3
LVL 77

Accepted Solution

peter57r earned 300 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.
LVL 47

Expert Comment

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

Author Comment

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


Author Comment

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


Author Comment

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


Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

863 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

27 Experts available now in Live!

Get 1:1 Help Now