• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 990
  • Last Modified:

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)

Open in new window

0
gdunn59
Asked:
gdunn59
  • 9
  • 8
1 Solution
 
jerryb30Commented:
Do you have null values in Manager_Name or Auditor_Name?
0
 
gdunn59Author Commented:
Yes there are some Manager and Auditor names that are null.
0
 
Helen FeddemaCommented:
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).
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
gdunn59Author Commented:
Helen_Feddema:

So how would I incorporate that in this query statement:

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))

Thanks,
gdunn59
0
 
jerryb30Commented:
FirstMgrName: iif(isnull([Mnager_Name], null, 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)))
0
 
jerryb30Commented:
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.
0
 
gdunn59Author Commented:
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
0
 
jerryb30Commented:
FirstMgrName: iif(isnull([Manager_Name]), null, 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)))
I think. I missed an paren.
0
 
gdunn59Author Commented:
jerryb30:

Still getting invalid syntax.

Thanks,
gdunn59
0
 
jerryb30Commented:
select IIf(IsNull([Manager_Name]),Null,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))) AS FirstMgrName from table1

is working for me in Access 2003.
0
 
gdunn59Author Commented:
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
0
 
gdunn59Author Commented:
I have to step away for a bit.  Be back on line later.

Thanks,
gdunn59
0
 
jerryb30Commented:
Confused a bit. Can you post a sanitized DB, including a null Manager_Name?
0
 
jerryb30Commented:
I can handle up  to Access 2010.
0
 
gdunn59Author Commented:
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:
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))

Open in new window


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))

Open in new window


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
0
 
jerryb30Commented:
In this instance, you had two related fields using manager_name, so both fields had to be adjusted.
FirstMgrName: IIf(IsNull([Manager_name]),Null,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: IIf(IsNull([manager_name]),Null,Left([Manager_Name],InStr([Manager_Name],",")-1))

You will have to do the same thing for auditor_name if you might have a null value.

Instr and Instrrev do not like null values, especially when you are adding or subtracting from those values.

Oddly, in access 2003, it only returns an #error# when the field is null. (Or, if you have a manager_name with no spaces or commas. If that CAN happen, you'll need to check for  instr or instrrev being 0.)

I would recommend that Manager_name and auditor_name be broken into multiple fields in the root table(s). Well, any names. And don't use [Name] as a field name in either tables or queries, as it is a reserved word. (Although Access uses [Name] in msysobjects, which can return some oddities.)
0
 
gdunn59Author Commented:
jerryb30:

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)

Open in new window


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))

Open in new window


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))

Open in new window

0
 
jerryb30Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now