Solved

Getting Invalid Procedure Call When Running My Query

Posted on 2013-06-20
5
717 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
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 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.
0
 

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
 

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
 

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

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