gdunn59
asked on
Data Type Mismatch with IIF Statements and InStr/InStrRev
I have a query with the following IIF Statements and/or InStr/InStrRev, and when I run the query I'm getting a "Data Type Mismatch Error", can't figure out why.
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)
Do you have null values in Manager_Name or Auditor_Name?
ASKER
Yes there are some Manager and Auditor names that are null.
Then you need to screen out the null values, perhaps in a query, or in the expression itself, using Nz (check if Nz([Manager_Name]) = "", and don't parse it in that case).
ASKER
Helen_Feddema:
So how would I incorporate that in this query statement:
FirstMgrName: IIf(InStrRev([Manager_Name ]," ")=InStr([Manager_Name],", ")+1,Mid([ Manager_Na me],InStr( [Manager_N ame],",")+ 2),Mid([Ma nager_Name ],InStr([M anager_Nam e],",")+2, InStrRev([ Manager_Na me]," ")-InStr([Manager_Name],", ")-2))
Thanks,
gdunn59
So how would I incorporate that in this query statement:
FirstMgrName: IIf(InStrRev([Manager_Name
Thanks,
gdunn59
FirstMgrName: iif(isnull([Mnager_Name], null, IIf(InStrRev([Manager_Name ]," ")=InStr([Manager_Name],", ")+1,Mid([ Manager_Na me],InStr( [Manager_N ame],",")+ 2),Mid([Ma nager_Name ],InStr([M anager_Nam e],",")+2, InStrRev([ Manager_Na me]," ")-InStr([Manager_Name],", ")-2)))
Of course, you may want to try a copy of your table with all nulls removed, and make sure there isn't something else causing a problem, too.
ASKER
jerryb30:
It was because of null values. I removed the records that contained nulls in the Employee, Manager or Auditor fields.
I tried your query statement in your ID: 39279647 posting, but I get an error about invalid syntax. Can you give me the correct syntax?
In the meantime, I just went ahead and added "Not Null" as the criteria on those 3 fields, so in the future if there are any nulls it will ignore them.
Thanks,
gdunn59
It was because of null values. I removed the records that contained nulls in the Employee, Manager or Auditor fields.
I tried your query statement in your ID: 39279647 posting, but I get an error about invalid syntax. Can you give me the correct syntax?
In the meantime, I just went ahead and added "Not Null" as the criteria on those 3 fields, so in the future if there are any nulls it will ignore them.
Thanks,
gdunn59
FirstMgrName: iif(isnull([Manager_Name]) , null, IIf(InStrRev([Manager_Name ]," ")=InStr([Manager_Name],", ")+1,Mid([ Manager_Na me],InStr( [Manager_N ame],",")+ 2),Mid([Ma nager_Name ],InStr([M anager_Nam e],",")+2, InStrRev([ Manager_Na me]," ")-InStr([Manager_Name],", ")-2)))
I think. I missed an paren.
I think. I missed an paren.
ASKER
jerryb30:
Still getting invalid syntax.
Thanks,
gdunn59
Still getting invalid syntax.
Thanks,
gdunn59
select IIf(IsNull([Manager_Name]) ,Null,IIf( InStrRev([ Manager_Na me]," ")=InStr([Manager_Name],", ")+1,Mid([ Manager_Na me],InStr( [Manager_N ame],",")+ 2),Mid([Ma nager_Name ],InStr([M anager_Nam e],",")+2, InStrRev([ Manager_Na me]," ")-InStr([Manager_Name],", ")-2))) AS FirstMgrName from table1
is working for me in Access 2003.
is working for me in Access 2003.
ASKER
Ok. I got it to work, so not getting the syntax error anymore, but unless I have "Not Null" as the criteria for the Employee, Manager and Auditor, I'm still getting the "Data Type Mismatch" error, which is the initial reason I posted.
Thanks,
gdunn59
Thanks,
gdunn59
ASKER
I have to step away for a bit. Be back on line later.
Thanks,
gdunn59
Thanks,
gdunn59
Confused a bit. Can you post a sanitized DB, including a null Manager_Name?
I can handle up to Access 2010.
ASKER
jerryb30:
Ok. I have Access 2007.
I've attached the database with a Manager Name that contains a null. The query that I'm getting the Data Mismatch error on is "qryAssocReport".
The same error happens on the Employee and Auditor Names:
Employee:
Auditor Name:
I'm not sure if the other fields that have InStr statements (like the Last Names for these) in them is causing issues as well, or just the ones that have IIf statements and InStr statements.
Thanks,
gdunn59
EE-BETA-Audit-Database-SQL-Relea.accdb
Ok. I have Access 2007.
I've attached the database with a Manager Name that contains a null. The query that I'm getting the Data Mismatch error on is "qryAssocReport".
The same error happens on the Employee and Auditor Names:
Employee:
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))
Auditor Name:
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))
I'm not sure if the other fields that have InStr statements (like the Last Names for these) in them is causing issues as well, or just the ones that have IIf statements and InStr statements.
Thanks,
gdunn59
EE-BETA-Audit-Database-SQL-Relea.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jerryb30:
Your last posting worked.
Thanks much!
Here is my final code that worked:
For Employee:
For Manager:
For Auditor:
Your last posting worked.
Thanks much!
Here is my final code that worked:
For Employee:
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)
For Manager:
FirstMgrName: IIf(IsNull([Manager]),Null,IIf(InStrRev([Manager]," ")=InStr([Manager],",")+1,Mid([Manager],InStr([Manager],",")+2),Mid([Manager],InStr([Manager],",")+2,InStrRev([Manager]," ")-InStr([Manager],",")-2)))
LastMgrName: IIf(IsNull([Manager]),Null,Left([Manager],InStr([Manager],",")-1))
For Auditor:
FirstAuditorName: IIf(IsNull([Auditor_Name]),Null,IIf(InStrRev([Auditor_Name]," ")=InStr([Auditor_Name],",")+1,Mid([Auditor_Name],InStr([Auditor_Name],",")+2),Mid([Auditor_Name],InStr([Auditor_Name],",")+2,InStrRev([Auditor_Name]," ")-InStr([Auditor_Name],",")-2)))
LastAuditorName: IIf(IsNull([Auditor_Name]),Null,Left([Auditor_Name],InStr([Auditor_Name],",")-1))
Glad to help, but you would be better helped, if this is a beta database, to deal with how names are broken down/entered. And, maybe include 'Not Assigned' or 'Unknown' for the null fields, especially for Manager.