Do Not Include Criteria

A

antmorano

Hello All:

I have a question that is quite perplexing. I have 9 lines of
criteria that sorts retirees on a multitude of factors. One group
that cannot be included in the section is where the [STATUS OF
COVERAGE-RETIREE] AND [STATUS OF COVERAGE-SPOUSE] are "CANCELLED".
There are many other criteria that involve age. I need this criteria
regardless of age and any other factors not to be included if the two
fields have "CANCELLED" them. Both statuses are individual columns.
Any help would be greatly appreciated.

-Anthony Morano
Pension Fund Intern
 
K

KARL DEWEY

It may be difficult to see how to do in design view and get it right so you
need to go to SQL view. Then in the WHERE statement you can modify what you
have.

WHERE (Criteria That Applies to All) AND ( (Ctrteria One) OR (Criteria Two)
OR (Criteria Three) )

WHERE ([STATUS OF COVERAGE-RETIREE] <> "CANCELLED" AND [STATUS OF
COVERAGE-SPOUSE] <> "CANCELLED") AND ( (Ctrteria One) OR (Criteria Two) OR
(Criteria Three) )
 
A

antmorano

Karl- I appreciate the efficient response. I found the area in my SQL
view and it looks like this:

((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND (([All Plans].
[Status of Coverage- Spouse])<>"CANCELLED"));

I'm not sure what you mean by modifying the statement. Do you think
you could help me a little more.

-Anthony Morano
 
K

KARL DEWEY

There are many other criteria that involve age.
The part you posted did not have anything about age.

Post the complete SQL statement.
 
A

antmorano

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB], [All
Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date], [All
Plans].[Retirement Date], [All Plans].Address, [All Plans].City, [All
Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))) AS [Retiree Age], IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",
[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))) AS [Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND (([All Plans].[Status
of Coverage- Retiree])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-IIf(Format([Retiree
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)) OR ((([All Plans].
[Chain Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All
Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Spouse Eligibility Date])<Date())
AND (([All Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND
(([All Plans].[Status of Coverage- Spouse])<>"CANCELLED") AND
((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)
AND ((IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],
[TODAY])-IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))))<65)) OR ((([All Plans].[Chain Sequence Number])="5001") AND
(([All Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Retiree
DOD]) Is Not Null) AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)) OR ((([All Plans].
[Chain Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All
Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Spouse DOD]) Is Not Null) AND (([All
Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND ((IIf([Retiree
DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65))
OR ((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])="CANCELLED") AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)) OR ((([All Plans].
[Chain Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All
Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Status of Coverage-
Spouse])="CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-IIf(Format([Retiree
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)) OR ((([All Plans].
[Chain Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All
Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Spouse Eligibility
Date])<Date()) AND ((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",
[Retiree DOB],[TODAY])-IIf(Format([Retiree
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))>65) AND ((IIf([Spouse
DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-
IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65))
OR ((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-IIf(Format([Retiree
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65) AND ((IIf([Spouse
DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-
IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))>65))
OR ((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All
Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])="CANCELLED")) OR ((([All Plans].[Chain Sequence
Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All
Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Spouse Eligibility Date])<Date())
AND (([All Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND
(([All Plans].[Status of Coverage- Spouse])<>"CANCELLED") AND
((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65)
AND ((IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],
[TODAY])-IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))))<65));
 
K

KARL DEWEY

I made a couple of changes to include replacing [TODAY] with Date() so as to
use the current date. Remember pasting and posting inserts returns instead
of wraping. I paste and then start from bottom up to add spaces and remove
returns.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last Name],
[All Plans].[Retiree First Name], [All Plans].[Retiree SSN], [All
Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB], [All
Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date], [All
Plans].[Retirement Date], [All Plans].Address, [All Plans].City, [All
Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse Gender],
[All Plans].[Spouse Eligibility Date], [All Plans].[Retiree DOD], [All
Plans].[Spouse DOD], [All Plans].[Date of Entry], [All Plans].[Status of
Coverage- Retiree], [All Plans].[Status of Coverage- Spouse], IIf([Retiree
DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS [Retiree Age], IIf([Spouse DOD]
Is Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))) AS [Spouse Age]
FROM [All Plans]
WHERE ((([All Plans].[Chain Sequence Number])="5001") AND (([All
Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All Plans].Plan)="III" Or
([All Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or ([All
Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility Date])<Date())
AND (([All Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND (([All
Plans].[Status of Coverage- Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND (([All Plans].[Spouse
Eligibility Date])<Date()) AND (([All Plans].[Status of Coverage-
Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65) AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Retiree DOD]) Is
Not Null) AND (([All Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND
(([All Plans].[Status of Coverage- Spouse])<>"CANCELLED") AND ((IIf([Spouse
DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND (([All Plans].[Spouse DOD]) Is
Not Null) AND (([All Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND
(([All Plans].[Status of Coverage- Spouse])<>"CANCELLED") AND ((IIf([Retiree
DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))>65) AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Retiree Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65) AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))>65)) OR ((([All Plans].[Chain
Sequence Number])="5001") AND (([All Plans].Plan)="I" Or ([All
Plans].Plan)="II" Or ([All Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or
([All Plans].Plan)="IVL" Or ([All Plans].Plan)="IVLN" Or ([All
Plans].Plan)="IVN") AND (([All Plans].Fund)="Welfare") AND (([All
Plans].[Spouse Eligibility Date])<Date()) AND (([All Plans].[Status of
Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status of Coverage-
Spouse])<>"CANCELLED")) OR ((([All Plans].[Chain Sequence Number])="5001")
AND (([All Plans].Plan)="I" Or ([All Plans].Plan)="II" Or ([All
Plans].Plan)="III" Or ([All Plans].Plan)="IV" Or ([All Plans].Plan)="IVL" Or
([All Plans].Plan)="IVLN" Or ([All Plans].Plan)="IVN") AND (([All
Plans].Fund)="Welfare") AND (([All Plans].[Retiree Eligibility Date])<Date())
AND (([All Plans].[Spouse Eligibility Date])<Date()) AND (([All
Plans].[Status of Coverage- Retiree])<>"CANCELLED") AND (([All Plans].[Status
of Coverage- Spouse])<>"CANCELLED") AND ((IIf([Retiree DOD] Is
Null,Abs(DateDiff("yyyy",[Retiree DOB],Date())-IIf(Format([Retiree
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65) AND ((IIf([Spouse DOD] Is
Null,Abs(DateDiff("yyyy",[Spouse DOB],Date())-IIf(Format([Spouse
DOB],"mmdd")<=Format(Date(),"mmdd"),0,1))))<65));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Confused About Criteria 2
Query Does Not Pick Up Data 5
Criteria Expression Problem 3
Control Source in Form Doesn't Display Query 7
Specify Date to Calculate 1
Query Does Not Count "2" Records 1
Obtaining Count of Records and Placing in Report 1
Criteria Problems 1

Top