Iff then, iff not function

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
331
Office Version
  1. 365
Platform
  1. Windows
I am needing to compare multiple levels of information and need help! I'll provide the formula below I am trying to use.

Iff [Work_Family] = "1211" And [Oper-ID] contains "1710-Assy", "Error", iff [Work_Family] does not = "1211" And [Oper-ID] does not contain "1710-Assy", "Error", "No Error"

If an order contains that work family and oper ID, then there is an error. If an order is any other family and does not contain that Oper ID then there is an error.

And there isnt just one line per order. There can be up to 10 lines per work order.

Another thought I was trying to figure out was to filter on only orders that meet that criteria without using a formula. Just wasn't sure how to do that either.

*This is my first querie, sorry for the naivety*
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Your syntax is wrong, if that is what you are putting into your application. See iif function at https://www.techonthenet.com/access/functions/advanced/iif.php

Also, for the "contains" you'll need to use "LIKE" or "Instr" ... I'm not sure which. See https://www.mrexcel.com/forum/excel-questions/367969-vba-contains-like-statement.html

Lastly, your logic is faulty. If condition 1 is false, iif will execute the third piece of the function ... your proposal for the third piece logically says the opposite of the first piece. So you are saying, "If condition 1 is true, produce an error, and if condition 1 is not true then produce an error, otherwise there are no errors." It will therefore, most likely, always produce "No error" ... it makes no logical sense. I'm sure there's a term for this ... but I don't know it. My assumption is you mean, "If condition 1 is true, then produce an "error", else if condition 1 is false then there is "no error".

Basically, you should try something like:

Code: 
iif(And([LEFT][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] like "*1710-Assy*"), "Error", "No Error")[/FONT][/COLOR][/LEFT]
 
Upvote 0
AlexB123 said:
Your syntax is wrong, if that is what you are putting into your application. See iif function at https://www.techonthenet.com/access/functions/advanced/iif.php

Also, for the "contains" you'll need to use "LIKE" or "Instr" ... I'm not sure which. See https://www.mrexcel.com/forum/excel-questions/367969-vba-contains-like-statement.html

Lastly, your logic is faulty. If condition 1 is false, iif will execute the third piece of the function ... your proposal for the third piece logically says the opposite of the first piece. So you are saying, "If condition 1 is true, produce an error, and if condition 1 is not true then produce an error, otherwise there are no errors." It will therefore, most likely, always produce "No error" ... it makes no logical sense. I'm sure there's a term for this ... but I don't know it. My assumption is you mean, "If condition 1 is true, then produce an "error", else if condition 1 is false then there is "no error".

Basically, you should try something like:

Code: 
iif(And([LEFT][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] like "*1710-Assy*"), "Error", "No Error")[/FONT][/COLOR][/LEFT]
Click to expand...

I must not have explained that correctly. Let me try again.

The conditions I want to review are as follows:

Condition 1: Iff 1211 and "Assy" then produce an error.
Condition 2: iff not 1211 and no "assy" produce an error.

1211 should not have an assembly code
All other groups (there are a lot) must have an assembly code.

Therefore I am looking for all 1211 with assy codes and everything that is not 1211 that does not have assy codes.

Just because 1211 does not have assy, doesn't mean there are no errors.
 
Upvote 0
OK. There a couple ways to do this, I think. All similar. Try:

Code: 
[LEFT][COLOR=#333333][FONT=monospace]iif(And([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] LIKE "*1710-Assy*"), "Error", [LEFT][COLOR=#333333][FONT=monospace]iif(And([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] <> 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] NOTLIKE "*1710-Assy*"), "Error", "No Error"[/FONT][/COLOR][/FONT][/COLOR][/LEFT]
))[/FONT][/COLOR]
[/FONT][/COLOR][/LEFT]

OR

Code: 
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]iif(OR(AND([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] LIKE "*1710-Assy*"),[LEFT][COLOR=#333333][FONT=monospace]And([/FONT][/COLOR][/LEFT][/FONT][/COLOR][LEFT][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][COLOR=#333333][FONT=Verdana][Work_Family] <> 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] NOTLIKE "*1710-Assy*"))[/FONT][/COLOR], "Error", [COLOR=#333333][FONT=Verdana]"No Error"[/FONT][/COLOR])[/FONT][/COLOR]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
​
 
Upvote 0
AlexB123 said:
OK. There a couple ways to do this, I think. All similar. Try:

Code: 
[LEFT][COLOR=#333333][FONT=monospace]iif(And([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] LIKE "*1710-Assy*"), "Error", [LEFT][COLOR=#333333][FONT=monospace]iif(And([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] <> 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] NOTLIKE "*1710-Assy*"), "Error", "No Error"[/FONT][/COLOR][/FONT][/COLOR][/LEFT]
))[/FONT][/COLOR]
[/FONT][/COLOR][/LEFT]

OR

Code: 
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]iif(OR(AND([/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][Work_Family] = 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] LIKE "*1710-Assy*"),[LEFT][COLOR=#333333][FONT=monospace]And([/FONT][/COLOR][/LEFT][/FONT][/COLOR][LEFT][COLOR=#333333][FONT=monospace][COLOR=#333333][FONT=Verdana][COLOR=#333333][FONT=Verdana][Work_Family] <> 1211, [/FONT][/COLOR][COLOR=#333333][FONT=Verdana] [Oper-ID] NOTLIKE "*1710-Assy*"))[/FONT][/COLOR], "Error", [COLOR=#333333][FONT=Verdana]"No Error"[/FONT][/COLOR])[/FONT][/COLOR]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
​
Click to expand...

That worked to an extent!

It got me the 1211 with Assy but it returned all rows that were not "Assy". It is possible that it is not able to be done.

The 1211 result is great!
But say we have parts 1-100 all [Work_Family] are not 1211. There can be duplicates with other [Oper-ID]. I want it to search the list and if a Part never has a "Assy" [Oper-ID] then it will be on the report.

Maybe that is easier, not sure.
Thank you
 
Upvote 0
It should have worked ... what exactly do you mean that it "returned all rows that were not "Assy"?

Since we know the first part works, create a testing table without the 1211 records and just run it on those left. Do you get a result?
 
Upvote 0
I set up a testing database and ran the following as a calculated field:

Code: 
IIf([Work_Family]=1211 And [Oper-ID] Like "*1710-Assy*","Error",IIf([Work_Family]<>1211 And [Oper-ID] Not Like "*1710-Assy*","Error","No Error"))

This returned the results you were looking for.
 
Upvote 0
AlexB123 said:
I set up a testing database and ran the following as a calculated field:

Code: 
IIf([Work_Family]=1211 And [Oper-ID] Like "*1710-Assy*","Error",IIf([Work_Family]<>1211 And [Oper-ID] Not Like "*1710-Assy*","Error","No Error"))

This returned the results you were looking for.
Click to expand...

Hi. I guess I'm terrible at describing one I am looking for so I will try to make a table below of possibilities and then work from there.[TABLE="width: 500"]
<tbody>[TR]
[TD]Item_ID[/TD]
[TD]Work_Family[/TD]
[TD]Work-Center[/TD]
[/TR]
[TR]
[TD]22196782[/TD]
[TD]4200[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]22196782[/TD]
[TD]4200[/TD]
[TD]SHEAR[/TD]
[/TR]
[TR]
[TD]22196782[/TD]
[TD]4200[/TD]
[TD]PUNCH[/TD]
[/TR]
[TR]
[TD]441-101[/TD]
[TD]4200[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]441-101[/TD]
[TD]4200[/TD]
[TD]STRM[/TD]
[/TR]
[TR]
[TD]441-101[/TD]
[TD]4200[/TD]
[TD]MAZK[/TD]
[/TR]
[TR]
[TD]441-101[/TD]
[TD]4200[/TD]
[TD]ASSY[/TD]
[/TR]
[TR]
[TD]596103[/TD]
[TD]4200[/TD]
[TD]SHEAR[/TD]
[/TR]
[TR]
[TD]596103[/TD]
[TD]4200[/TD]
[TD]BRAKE[/TD]
[/TR]
[TR]
[TD]596103[/TD]
[TD]4200[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]596103[/TD]
[TD]4200[/TD]
[TD]STRM[/TD]
[/TR]
[TR]
[TD]89325[/TD]
[TD]1211[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]89325[/TD]
[TD]1211[/TD]
[TD]ASSY[/TD]
[/TR]
[TR]
[TD]9910456[/TD]
[TD]1211[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]9910456[/TD]
[TD]1211[/TD]
[TD]SHR[/TD]
[/TR]
[TR]
[TD]9910456[/TD]
[TD]1211[/TD]
[TD]BEND[/TD]
[/TR]
[TR]
[TD]8934520[/TD]
[TD]4200[/TD]
[TD]SAW[/TD]
[/TR]
[TR]
[TD]8934520[/TD]
[TD]4200[/TD]
[TD]ASSY[/TD]
[/TR]
</tbody>[/TABLE]

So I have provided examples of both. I want the report to look at all ITEM_ID and create a report for all ITEM_ID that are "4200" WORK_FAMILY but do not have "ASSY" in WORK_CENTER.

It would also report all ITEM_ID that are "1211" WORK_FAMILY and do have "ASSY" WORK_CENTER.

So for 4200, the errors would be:
22196782
596103

For 1211, the errors would be:
89325

I hope this provides clarity. Very sorry for any confusion.
 
Upvote 0
There are a number of problems here. Let's start with the code you are writing.

You are trying to create a report? What are you trying to create? A query to base the report off of? Or are you trying to create a calculated field in a table? Your initial question concerned an IIF statement. What is the purpose of producing an error or no error?

If you are trying to create a report, you need to write queries. To get the information you want, the data needs to be normalized ... that is, every row needs a unique identifier. Currently, in this example, you have repeated information ... there's no way to distinguish between "Item_ID" when there are three "22196782" fields. So you need a primary key.

Second, in "Item_ID" you have a dash in some of the numbers. You either need to clean the data when you are importing it, and remove the dash, and therefore every "Item_ID" will be a number ... or save that column as "Strings". Just be consistent.

Third, what happened to "Oper-ID"? Do you have columns that are full strings? I.e., descriptions? As in "Customer ordered ASSY", or is "Work-Center" always one key word?

What you need is two queries ... one result-set that for 4200 work-family without "ASSY" and another result-set for 1211 and "ASSY". You need to test and play with the queries until you get the correct results. You will then use the two queries as a record source for two reports. Now, you could combine the two queries so that you only need to produce one report, but that will be more complex. If you start with two simple queries, you can then build one that incorporates both, you just have to play around with it.

It sounds to me that you need to do a bit more research, then figure out what you want to do. Play with the formulas and queries and get your logic figured out, I can't write them for you or design your database.

Here's what I have done ... I created a test database with the data you provided, Access automatically created a column, which I named UID, of autonumbers for the primary key. Then I created Field1 to hold the calculated field, as originally requested. Here is that field:

Code: 
IIf([Work_Family]=1211 And [Work-Center]="Assy","Error",IIf([Work_Family]<>1211 And [Work-Center]<>"Assy","Error","No Error"))

A Query to select your 4200 with no ASSY:
Code: 
SELECT * FROM tblExample
WHERE [Work_Family] = 4200
AND [Work-Center] <> "ASSY";

A query for your 1211 and ASSY:
Code: 
SELECT * FROM tblExample
WHERE [Work_Family] = 1211
AND [Work-Center] = "ASSY";

Now, a third query, that selects both 1211 and 4200 based off of the calculated field returning "Error":
Code: 
SELECT * FROM tblExample
WHERE Field1 = "Error";


Play with these. When you respond, be very specific about what you tried, what worked, and what didn't. Provide examples of the code you used.
 
Upvote 0
A solution with plain SQL:
Code: 
select distinct
	Item_ID, Work_Family 
from 
	tblExample t1
where 
	Work_Family <> 1211 
	and not exists (
		select * from tblExample t2 
		where t2.Work_Family = t1.Work_Family 
			and t2.Item_ID = t1.Item_ID 
			and t2.Work_Center = "ASSY")

UNION ALL

select distinct
	Item_ID, Work_Family 
from 
	tblExample t1
where 
	t1.Work_Family = 1211 
	and exists (
		select * from tblExample t2 
		where t2.Work_Family = t1.Work_Family 
		and t2.Item_ID = t1.Item_ID 
		and t2.Work_Center = "ASSY")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
539
Davenil
D
rjbinney
  • Solved
Error 1004 - Saving Macros in Macro-Free Workbooks
Replies
6
Views
655
rjbinney
rjbinney
A
  • Question Question
Automation of Copy and Merge Data in Column A and B of all files in a folder. Order of Data Differs
Replies
2
Views
685
ashish002
A
B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
729
mumps
mumps
S
  • Question Question
Large project, first time calling subs and using error handling
Replies
0
Views
482
Shakeable_Drip
S

Forum statistics

Threads
1,223,565
Messages
6,173,089
Members
452,501
Latest member
musallam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top