I need to achieve the opposite of this ...

[padlocked17]

I have a large query that I'm attempting to narrow down the results based on the SectionID and a IsNull.

I've got this in the Query criteria that shows me all of the records I want to exclude from the query. How do I include all of the other records and exclude these?

=6 And [Fixed_ACC_DT] Is Null

SQL

SELECT FixedARMS.Unit, FixedARMS.SSAN, FixedARMS.Name, FixedARMS.LAST4, FixedARMS.Flyer_Type, FixedARMS.CPOS, tblTasks.Task_ID, tblTasks.Description, tblTasks.SectionID, tblTaskSections.SectionName, tblTaskSections.SectionPageNum, tblTaskSections.SectionRemark, tblTasks.TaskLineNum, tblTasks.TaskRemarks, FixedARMS.N, FixedARMS.S, FixedARMS.Last_ACC_DT, FixedARMS.Fixed_ACC_DT, FixedARMS.ARMS_Due_DT, FixedARMS.GeneratedDue, FixedARMS.Accomplished, FixedARMS.Remaining, FixedARMS.Frequency, FixedARMS.Due_Date_Flag, FixedARMS.WAIVER_Due_DT, ITS_Current_Other.Physical_ACC, ITS_Current_Other.Physical_Due, ITS_Current_Other.Physical_Avail, ITS_Current_Other.Physio_ACC, ITS_Current_Other.Physio_Due, ITS_Current_Other.RecordsReview_ACC, ITS_Current_Other.RecordsReview_Due, ITS_Current_Other.DNIF, TotalFlyTime.PrimMDS, TotalFlyTime.Total, TotalFlyTime.PrimAcft, [306090].[30], [306090].[60], [306090].[90], FixedARMS.SemiAnnual_REQ, FixedARMS.[1SemiAnnual_ACC], FixedARMS.[2SemiAnnual_ACC]
FROM ITS_Current_Other RIGHT JOIN (TotalFlyTime RIGHT JOIN (306090 RIGHT JOIN ((FixedARMS INNER JOIN tblTasks ON FixedARMS.Task_ID = tblTasks.Task_ID) LEFT JOIN tblTaskSections ON tblTasks.SectionID = tblTaskSections.SectionID) ON [306090].SSAN = FixedARMS.SSAN) ON TotalFlyTime.SSAN = FixedARMS.SSAN) ON ITS_Current_Other.SSAN = FixedARMS.SSAN
WHERE (((FixedARMS.SSAN)=xxxxxxxxx) AND ((tblTasks.SectionID)<>2) AND ((tblTasks.SectionID)=6 And [Fixed_ACC_DT] Is Null));

So basically what I want to do is:

IIf([SectionID]=6 And IsNull([Fixed_ACC_DT]),"EXCLUDE","DO NOTHING")

 

[boblarson]

How about

<> 6 And [Fixed_ACC_DT] Is Not Null

 

[rainman89]

so you want something like

<>6 and [fixed_acc_dt] Is Not Null ???

 

[padlocked17]

Well that generates 109 records and my "=6 And [Fixed_ACC_DT] Is Null" statement only generates 14 records and with no criteria at all I get 143 records.

I'm thinking I might have found the culprit. Maybe not. I've got a second set of records I need to exclude as well (Anything with a SectionID of 2).

Do I need two separate columns with SectionID or can I wrap it all up into one statement. I know I don't want an "OR" since it needs to be both.

 

[boblarson]

You can use

<> 6 And [Fixed_ACC_DT] Is Not Null AND [SectionID] <> 2

 

[padlocked17]

The other piece to it is that some of the other records with a SectionID of 5 as an example needs to have records included if the Fixed_ACC_DT Is Null

 

[rainman89]

if that is the case then you are going to want to use some or statements

 

[padlocked17]

Alright. Thanks for that help. I ended up using 3 Or Statements. If anyone has a better solution please let me know.

<6 And <>2

>6 And <>2

=6 And [Fixed_ACC_DT] Is Not Null

I can't say I'm 100% positive why this works ... but it seems that it does.