Solved

Data Type Mismatch with IIF Statements and InStr/InStrRev

Posted on 2013-06-26
18
849 Views
Last Modified: 2013-07-02
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
Comment
Question by:gdunn59
  • 9
  • 8
18 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39278986
Do you have null values in Manager_Name or Auditor_Name?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39279068
Yes there are some Manager and Auditor names that are null.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39279150
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:gdunn59
ID: 39279193
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39279647
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39279654
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39280213
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39280227
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39281958
jerryb30:

Still getting invalid syntax.

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39282492
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39283288
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39283289
I have to step away for a bit.  Be back on line later.

Thanks,
gdunn59
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39283354
Confused a bit. Can you post a sanitized DB, including a null Manager_Name?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39283355
I can handle up  to Access 2010.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39283540
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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 300 total points
ID: 39285575
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
 
LVL 1

Author Comment

by:gdunn59
ID: 39294758
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39294778
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IIF in access query 19 26
how to insert parameter value in table 2 23
Need return values from a stored procedure 8 21
Reformat SQL - so SSRS can read the columns 25 13
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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