Using IIF to outsort data

scotteriley

New Member
Joined
Jul 27, 2010
Messages
9
Currently, when I import data for a certain table, I run the data through several queries and only import the data that meets the criteria. I would like to add one more query but, I'm not clear on how to write the expression.

Within the table are a number of columns. One column is for Product Type and another is for Branch Number. What I would like to do is if the product type is INV and the Branch Number is "0", then I DO NOT want to include the data in the import.

Any thoughts?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,683
Office Version
2019
Platform
Windows
Instead of an IIF statement, put this in the respective fields criteria.

<>"INV" in the product field and <>0 in the Branch field. Make sure they are on the same line in the QBE.

This will exclude those records from your query.

Alan
 

scotteriley

New Member
Joined
Jul 27, 2010
Messages
9
That is a good point but, I only want to exclude if both statements are TRUE. I do not want to exclude all products INV or all Branch # 0.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,649
Office Version
365
Platform
Windows
Make sure they are on the same line in the QBE.
Did you notice the line above that Alan mentioned?
As long as both criteria are entered on the same line, it will treat the two criteria as AND, which sounds like what you want.
If you put the criteria on different criteria lines, it will be treated as OR.

Try it out and see for yourself.
 

scotteriley

New Member
Joined
Jul 27, 2010
Messages
9
After I created this query and selected RUN, the query excluded all product types INV and all Branch # 0s.

Any suggestions??
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,683
Office Version
2019
Platform
Windows
Post your SQL statement so we can diagnose better.

Alan
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,649
Office Version
365
Platform
Windows
Yes, it appears that didn't quite work out like I thought it would. Sorry about that.

If you go to the SQL editor, try typing it like this:
Code:
...
WHERE NOT ([Product]="INV" AND [Branch]=0)
 

scotteriley

New Member
Joined
Jul 27, 2010
Messages
9
Thank you for all your help, SQL statement posted below:

SELECT [RAW DATA INPUT].[Customer Last Update Dt], [RAW DATA INPUT].[Customer Cust First Contact Dt], [RAW DATA INPUT].[Customer Id], [RAW DATA INPUT].[Customer Branch Id], [RAW DATA INPUT].[Customer Primary Offr Emp Id], [RAW DATA INPUT].[Customer Name], [RAW DATA INPUT].[Customer Rif Ind], [RAW DATA INPUT].[Cust App Cd], [RAW DATA INPUT].[Cust To Acct Relationship Cd], [RAW DATA INPUT].[Cust Acct Id], [RAW DATA INPUT].[Account Open Dt], [RAW DATA INPUT].[Account Org Unit Id], [RAW DATA INPUT].[Account Acct Branch Id], [RAW DATA INPUT].[Account Rc Id], [RAW DATA INPUT].[Account Primary Offc Emp Id]
FROM [RAW DATA INPUT]
WHERE ((([RAW DATA INPUT].[Customer Branch Id])<>0) AND (([RAW DATA INPUT].[Cust App Cd])<>"INV"));
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,649
Office Version
365
Platform
Windows
Based on my last reply, and your last reply:

Code:
...
WHERE NOT (([RAW DATA INPUT].[Customer Branch Id]=0) AND ([RAW DATA INPUT].[Cust App Cd]="INV"));
 

Watch MrExcel Video

Forum statistics

Threads
1,102,289
Messages
5,485,916
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top