gdunn59
asked on
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:
Help please!!!!!
Thanks,
gdunn59
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)
The Total Row all have Expression.Help please!!!!!
Thanks,
gdunn59
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Does it matter if some of the names have a middle initial, and others don't?
Thanks,
gdunn59
Thanks,
gdunn59
ASKER
I've attached a spreadsheet with name examples for the Employee, Manager and Auditor.
Thanks,
gdunn59
Names.xlsx
Thanks,
gdunn59
Names.xlsx
ASKER
I was getting the error because some records contained null values in the Employee, Manager or Auditor fields.
Thanks,
gdunn59
Thanks,
gdunn59
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.