Using IIF to outsort data

[scotteriley]

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?

 

[alansidman]

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]

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]

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]

After I created this query and selected RUN, the query excluded all product types INV and all Branch # 0s.

Any suggestions??

 

[alansidman]

Post your SQL statement so we can diagnose better.

Alan

 

[Joe4]

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:

...
WHERE NOT ([Product]="INV" AND [Branch]=0)

 

[scotteriley]

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]

Based on my last reply, and your last reply:

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

 

[scotteriley]

I will try that now, Thanks Joe!!