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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
After I created this query and selected RUN, the query excluded all product types INV and all Branch # 0s.

Any suggestions??
 
Upvote 0
Post your SQL statement so we can diagnose better.

Alan
 
Upvote 0
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)
 
Upvote 0
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"));
 
Upvote 0
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"));
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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