YES/NO not working

D

Dan @BCBS

I have a somewhat long Query that pulls from two tables.
When I add WHERE (((tblTrackingData.EXTENSION)=True)......
It does not give me only the True EXTENSION.

Here is the code:


SELECT DISTINCTROW tblTrackingData.ICNNO, tblTrackingData.TR_EXTENSION,
tblTrackingData.TR_GBU, tblTrackingData.TR_PRODUCT,
tblTrackingData.TR_9000CAUSECODE, tblTrackingData.TR_INQUIRYTYPE,
tblTrackingData.TR_CASETYPE, tblTrackingData.TR_DATE_TIMERCVD_HOI,
IIf([tr_date_timercvd_hoi] Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS RcvdPeriod,
tblTrackingData.TR_CLOSEDATE, IIf([tr_closedate] Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS ClosePeriod,
tblTrackingData.TR_ACKNOWLTR, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),IIf([tr_product]="MM"
And [tr_casetype] In
("C","P"),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate]),businessdays([tr_date_timercvd_hoi],[tr_closedate]))))
AS TimeToClose, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate])))
AS TimeToCloseErisa, IIf([TR_Acknowltr] Is Null Or
[rcvdperiod]=0,Null,IIf([TR_Acknowltr] Is Not
Null,businessdays([tr_Date_timeRcvd_hoi],[tr_Acknowltr]),businessdays([tr_Date_timeRcvd_hoi],Date())))
AS AckCycle, IIf([tr_product]="HO" And [tr_expedited]=False And
[ackcycle]<=5,1,0) AS CommAckLetter, IIf([tr_product]="HO" And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS CommCasesRcvd,
IIf([tr_product]="HO" And [TR_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=15 And
[tr_expedited]=False,1,0) AS Comm15ERISA, IIf([tr_product]="HO" And
[tr_casetype] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Comm30ERISA,
IIf([tr_product]="HO" And [timetoclose]<=30 And [tr_extension]=False And
[tr_expedited]=False,1,0) AS Comm30, IIf([tr_product]="HO" And
[timetoclose]<=45 And [tr_extension]=True And [tr_expedited]=False,1,0) AS
Comm45, IIf([tr_product]="HO" And [TimeToCloseErisa]<=60 And
[tr_expedited]=False,1,0) AS Comm60, IIf([tr_product]="HO" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS CommCasesClosed,
IIf([tr_product]="HO" And [tr_expedited]=False And [tr_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [closeperiod]=1,1,0) AS
CommCasesClosed15ERISA, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_CaseType] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[closeperiod]=1,1,0) AS CommCasesClosed30ERISA, IIf([tr_product]="HO" And
[tr_expedited]=False And [tr_extension]=False And [closeperiod]=1,1,0) AS
CommCasesClosed30, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_extension]=True And [closeperiod]=1,1,0) AS CommCasesClosed45,
IIf([tr_expedited]=True And [tr_product]="HO" And [rcvdperiod]=1,1,0) AS
ExpeditedGrievCasesRcvd, IIf([tr_expedited]=True And [tr_product]="HO" And
[timetoclose]<=72,1,0) AS ExpeditedGriev, IIf([tr_expedited]=True And
[tr_product]="HO" And [closeperiod]=1,1,0) AS ExpeditedGrievCasesClosed,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[rcvdperiod]=1,1,0) AS ExpeditedCasesRcvd, IIf([tr_expedited]=True And
([tr_product]="MM" Or [tr_product]="AY") And [timetoclose]<=72,1,0) AS
Expedited, IIf([tr_expedited]=True And ([tr_product]="MM" Or
[tr_product]="AY") And [closeperiod]=1,1,0) AS ExpeditedCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [rcvdperiod]=1,1,0) AS MedicareGrievCasesRcvd,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_inquirytype]="GR" And
[ackcycle]<=5 And [tr_expedited]=False And [rcvdperiod]=1,1,0) AS
MedicareGrievAck, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [timetoclose]<=30 And [tr_expedited]=False,1,0) AS
Medicare30G, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Medicare30GERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [closeperiod]=1,1,0) AS MedicareGrievCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS MedicareGrievCasesClosedERISA, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="C" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicareClaimsCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="C" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicareClaimsAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS MedicareClaimsCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[timetoclose]<=60,1,0) AS Medicare60, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicarePreServCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[tr_extension]=False And [closeperiod]=1,1,0) AS
MedicarePreServCasesClosed30, IIf(([tr_product]="MM" Or [tr_product]="AY")
And [tr_casetype]="P" And [tr_expedited]=False And [tr_extension]=True And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed44, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="P" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicarePreServAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=30 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare30A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=44 And [tr_casetype]="P" And [tr_extension]=True And
[tr_expedited]=False,1,0) AS Medicare44, IIf([tr_inquirytype]="OD" And
[rcvdperiod]=1,1,0) AS OrgDetermRcvd, IIf([tr_inquirytype]="OD" And
[closeperiod]=1,1,0) AS OrgDetermClosed, IIf([tr_inquirytype]="OD" And
[timetoclose]<=72,1,0) AS OrgDeterm, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=72 And
[tr_expedited]=False,1,0) AS Medicare72GERISA, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS ExpeditedMedicareGrievCasesClosedERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=44 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare44A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=44 And [tr_expedited]=False,1,0) AS Medicare44GERISA,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=72,1,0) AS Expedited72, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=1 And
[tr_expedited]=False,1,0) AS Medicare1GERISA
FROM tblTrackingData LEFT JOIN tblExpedited ON tblTrackingData.ICNNO =
tblExpedited.ICNNO
WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
 
O

OfficeDev18 via AccessMonster.com

That's because you band all the And conditions before the OR (in the WHERE
statement) and you band separately all the And conditions after the OR. So
what you have is 'either this bunch of conditions or that bunch of
conditions'. Try going from

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));

to

WHERE ((tblTrackingData.TR_EXTENSION)=True) AND
((((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])));

What I did was isolate your extension=true clause, and put all the other
conditions in, subject to the OR condition. The extension=true requirement
remains inviolate.

It's all in the parentheses.

HTH
I have a somewhat long Query that pulls from two tables.
When I add WHERE (((tblTrackingData.EXTENSION)=True)......
It does not give me only the True EXTENSION.

Here is the code:

SELECT DISTINCTROW tblTrackingData.ICNNO, tblTrackingData.TR_EXTENSION,
tblTrackingData.TR_GBU, tblTrackingData.TR_PRODUCT,
tblTrackingData.TR_9000CAUSECODE, tblTrackingData.TR_INQUIRYTYPE,
tblTrackingData.TR_CASETYPE, tblTrackingData.TR_DATE_TIMERCVD_HOI,
IIf([tr_date_timercvd_hoi] Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS RcvdPeriod,
tblTrackingData.TR_CLOSEDATE, IIf([tr_closedate] Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS ClosePeriod,
tblTrackingData.TR_ACKNOWLTR, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),IIf([tr_product]="MM"
And [tr_casetype] In
("C","P"),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate]),businessdays([tr_date_timercvd_hoi],[tr_closedate]))))
AS TimeToClose, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate])))
AS TimeToCloseErisa, IIf([TR_Acknowltr] Is Null Or
[rcvdperiod]=0,Null,IIf([TR_Acknowltr] Is Not
Null,businessdays([tr_Date_timeRcvd_hoi],[tr_Acknowltr]),businessdays([tr_Date_timeRcvd_hoi],Date())))
AS AckCycle, IIf([tr_product]="HO" And [tr_expedited]=False And
[ackcycle]<=5,1,0) AS CommAckLetter, IIf([tr_product]="HO" And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS CommCasesRcvd,
IIf([tr_product]="HO" And [TR_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=15 And
[tr_expedited]=False,1,0) AS Comm15ERISA, IIf([tr_product]="HO" And
[tr_casetype] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Comm30ERISA,
IIf([tr_product]="HO" And [timetoclose]<=30 And [tr_extension]=False And
[tr_expedited]=False,1,0) AS Comm30, IIf([tr_product]="HO" And
[timetoclose]<=45 And [tr_extension]=True And [tr_expedited]=False,1,0) AS
Comm45, IIf([tr_product]="HO" And [TimeToCloseErisa]<=60 And
[tr_expedited]=False,1,0) AS Comm60, IIf([tr_product]="HO" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS CommCasesClosed,
IIf([tr_product]="HO" And [tr_expedited]=False And [tr_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [closeperiod]=1,1,0) AS
CommCasesClosed15ERISA, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_CaseType] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[closeperiod]=1,1,0) AS CommCasesClosed30ERISA, IIf([tr_product]="HO" And
[tr_expedited]=False And [tr_extension]=False And [closeperiod]=1,1,0) AS
CommCasesClosed30, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_extension]=True And [closeperiod]=1,1,0) AS CommCasesClosed45,
IIf([tr_expedited]=True And [tr_product]="HO" And [rcvdperiod]=1,1,0) AS
ExpeditedGrievCasesRcvd, IIf([tr_expedited]=True And [tr_product]="HO" And
[timetoclose]<=72,1,0) AS ExpeditedGriev, IIf([tr_expedited]=True And
[tr_product]="HO" And [closeperiod]=1,1,0) AS ExpeditedGrievCasesClosed,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[rcvdperiod]=1,1,0) AS ExpeditedCasesRcvd, IIf([tr_expedited]=True And
([tr_product]="MM" Or [tr_product]="AY") And [timetoclose]<=72,1,0) AS
Expedited, IIf([tr_expedited]=True And ([tr_product]="MM" Or
[tr_product]="AY") And [closeperiod]=1,1,0) AS ExpeditedCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [rcvdperiod]=1,1,0) AS MedicareGrievCasesRcvd,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_inquirytype]="GR" And
[ackcycle]<=5 And [tr_expedited]=False And [rcvdperiod]=1,1,0) AS
MedicareGrievAck, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [timetoclose]<=30 And [tr_expedited]=False,1,0) AS
Medicare30G, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Medicare30GERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [closeperiod]=1,1,0) AS MedicareGrievCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS MedicareGrievCasesClosedERISA, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="C" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicareClaimsCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="C" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicareClaimsAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS MedicareClaimsCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[timetoclose]<=60,1,0) AS Medicare60, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicarePreServCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[tr_extension]=False And [closeperiod]=1,1,0) AS
MedicarePreServCasesClosed30, IIf(([tr_product]="MM" Or [tr_product]="AY")
And [tr_casetype]="P" And [tr_expedited]=False And [tr_extension]=True And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed44, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="P" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicarePreServAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=30 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare30A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=44 And [tr_casetype]="P" And [tr_extension]=True And
[tr_expedited]=False,1,0) AS Medicare44, IIf([tr_inquirytype]="OD" And
[rcvdperiod]=1,1,0) AS OrgDetermRcvd, IIf([tr_inquirytype]="OD" And
[closeperiod]=1,1,0) AS OrgDetermClosed, IIf([tr_inquirytype]="OD" And
[timetoclose]<=72,1,0) AS OrgDeterm, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=72 And
[tr_expedited]=False,1,0) AS Medicare72GERISA, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS ExpeditedMedicareGrievCasesClosedERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=44 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare44A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=44 And [tr_expedited]=False,1,0) AS Medicare44GERISA,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=72,1,0) AS Expedited72, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=1 And
[tr_expedited]=False,1,0) AS Medicare1GERISA
FROM tblTrackingData LEFT JOIN tblExpedited ON tblTrackingData.ICNNO =
tblExpedited.ICNNO
WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
Click to expand...
 
D

Dan @BCBS

Almost::::
If I remove the OR statement from the middle it works.
I've been trying to add the OR back in but not having much luck..

OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN"





OfficeDev18 via AccessMonster.com said:
That's because you band all the And conditions before the OR (in the WHERE
statement) and you band separately all the And conditions after the OR. So
what you have is 'either this bunch of conditions or that bunch of
conditions'. Try going from

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));

to

WHERE ((tblTrackingData.TR_EXTENSION)=True) AND
((((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])));

What I did was isolate your extension=true clause, and put all the other
conditions in, subject to the OR condition. The extension=true requirement
remains inviolate.

It's all in the parentheses.

HTH
I have a somewhat long Query that pulls from two tables.
When I add WHERE (((tblTrackingData.EXTENSION)=True)......
It does not give me only the True EXTENSION.

Here is the code:

SELECT DISTINCTROW tblTrackingData.ICNNO, tblTrackingData.TR_EXTENSION,
tblTrackingData.TR_GBU, tblTrackingData.TR_PRODUCT,
tblTrackingData.TR_9000CAUSECODE, tblTrackingData.TR_INQUIRYTYPE,
tblTrackingData.TR_CASETYPE, tblTrackingData.TR_DATE_TIMERCVD_HOI,
IIf([tr_date_timercvd_hoi] Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS RcvdPeriod,
tblTrackingData.TR_CLOSEDATE, IIf([tr_closedate] Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS ClosePeriod,
tblTrackingData.TR_ACKNOWLTR, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),IIf([tr_product]="MM"
And [tr_casetype] In
("C","P"),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate]),businessdays([tr_date_timercvd_hoi],[tr_closedate]))))
AS TimeToClose, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate])))
AS TimeToCloseErisa, IIf([TR_Acknowltr] Is Null Or
[rcvdperiod]=0,Null,IIf([TR_Acknowltr] Is Not
Null,businessdays([tr_Date_timeRcvd_hoi],[tr_Acknowltr]),businessdays([tr_Date_timeRcvd_hoi],Date())))
AS AckCycle, IIf([tr_product]="HO" And [tr_expedited]=False And
[ackcycle]<=5,1,0) AS CommAckLetter, IIf([tr_product]="HO" And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS CommCasesRcvd,
IIf([tr_product]="HO" And [TR_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=15 And
[tr_expedited]=False,1,0) AS Comm15ERISA, IIf([tr_product]="HO" And
[tr_casetype] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Comm30ERISA,
IIf([tr_product]="HO" And [timetoclose]<=30 And [tr_extension]=False And
[tr_expedited]=False,1,0) AS Comm30, IIf([tr_product]="HO" And
[timetoclose]<=45 And [tr_extension]=True And [tr_expedited]=False,1,0) AS
Comm45, IIf([tr_product]="HO" And [TimeToCloseErisa]<=60 And
[tr_expedited]=False,1,0) AS Comm60, IIf([tr_product]="HO" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS CommCasesClosed,
IIf([tr_product]="HO" And [tr_expedited]=False And [tr_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [closeperiod]=1,1,0) AS
CommCasesClosed15ERISA, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_CaseType] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[closeperiod]=1,1,0) AS CommCasesClosed30ERISA, IIf([tr_product]="HO" And
[tr_expedited]=False And [tr_extension]=False And [closeperiod]=1,1,0) AS
CommCasesClosed30, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_extension]=True And [closeperiod]=1,1,0) AS CommCasesClosed45,
IIf([tr_expedited]=True And [tr_product]="HO" And [rcvdperiod]=1,1,0) AS
ExpeditedGrievCasesRcvd, IIf([tr_expedited]=True And [tr_product]="HO" And
[timetoclose]<=72,1,0) AS ExpeditedGriev, IIf([tr_expedited]=True And
[tr_product]="HO" And [closeperiod]=1,1,0) AS ExpeditedGrievCasesClosed,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[rcvdperiod]=1,1,0) AS ExpeditedCasesRcvd, IIf([tr_expedited]=True And
([tr_product]="MM" Or [tr_product]="AY") And [timetoclose]<=72,1,0) AS
Expedited, IIf([tr_expedited]=True And ([tr_product]="MM" Or
[tr_product]="AY") And [closeperiod]=1,1,0) AS ExpeditedCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [rcvdperiod]=1,1,0) AS MedicareGrievCasesRcvd,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_inquirytype]="GR" And
[ackcycle]<=5 And [tr_expedited]=False And [rcvdperiod]=1,1,0) AS
MedicareGrievAck, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [timetoclose]<=30 And [tr_expedited]=False,1,0) AS
Medicare30G, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Medicare30GERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [closeperiod]=1,1,0) AS MedicareGrievCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS MedicareGrievCasesClosedERISA, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="C" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicareClaimsCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="C" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicareClaimsAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS MedicareClaimsCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[timetoclose]<=60,1,0) AS Medicare60, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicarePreServCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[tr_extension]=False And [closeperiod]=1,1,0) AS
MedicarePreServCasesClosed30, IIf(([tr_product]="MM" Or [tr_product]="AY")
And [tr_casetype]="P" And [tr_expedited]=False And [tr_extension]=True And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed44, IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="P" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicarePreServAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=30 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare30A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=44 And [tr_casetype]="P" And [tr_extension]=True And
[tr_expedited]=False,1,0) AS Medicare44, IIf([tr_inquirytype]="OD" And
[rcvdperiod]=1,1,0) AS OrgDetermRcvd, IIf([tr_inquirytype]="OD" And
[closeperiod]=1,1,0) AS OrgDetermClosed, IIf([tr_inquirytype]="OD" And
[timetoclose]<=72,1,0) AS OrgDeterm, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=72 And
[tr_expedited]=False,1,0) AS Medicare72GERISA, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS ExpeditedMedicareGrievCasesClosedERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=44 And [tr_extension]=False And [tr_expedited]=False,1,0) AS
Medicare44A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=44 And [tr_expedited]=False,1,0) AS Medicare44GERISA,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=72,1,0) AS Expedited72, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=1 And
[tr_expedited]=False,1,0) AS Medicare1GERISA
FROM tblTrackingData LEFT JOIN tblExpedited ON tblTrackingData.ICNNO =
tblExpedited.ICNNO
WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
Click to expand...
Click to expand...
 
J

John Spencer

Guessing, you might want something like the following. Note the addition of
the parentheses.

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
(
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
)


Stripping out the extra unneeded parens and consolidating everything

WHERE tblTrackingData.TR_EXTENSION=True AND
tblTrackingData.TR_INQUIRYTYPE<>"IN" And
tblTrackingData.TR_INQUIRYTYPE)<>"NC" AND

(tblTrackingData.TR_DATE_TIMERCVD_HOI Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate])
OR
tblTrackingData.TR_CLOSEDATE
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])


OfficeDev18 via AccessMonster.com said:
That's because you band all the And conditions before the OR (in the WHERE
statement) and you band separately all the And conditions after the OR. So
what you have is 'either this bunch of conditions or that bunch of
conditions'. Try going from

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));

to

WHERE ((tblTrackingData.TR_EXTENSION)=True) AND
((((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])));

What I did was isolate your extension=true clause, and put all the other
conditions in, subject to the OR condition. The extension=true requirement
remains inviolate.

It's all in the parentheses.

HTH
I have a somewhat long Query that pulls from two tables.
When I add WHERE (((tblTrackingData.EXTENSION)=True)......
It does not give me only the True EXTENSION.

Here is the code:

SELECT DISTINCTROW tblTrackingData.ICNNO, tblTrackingData.TR_EXTENSION,
tblTrackingData.TR_GBU, tblTrackingData.TR_PRODUCT,
tblTrackingData.TR_9000CAUSECODE, tblTrackingData.TR_INQUIRYTYPE,
tblTrackingData.TR_CASETYPE, tblTrackingData.TR_DATE_TIMERCVD_HOI,
IIf([tr_date_timercvd_hoi] Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS RcvdPeriod,
tblTrackingData.TR_CLOSEDATE, IIf([tr_closedate] Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate],1,0) AS ClosePeriod,
tblTrackingData.TR_ACKNOWLTR, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),IIf([tr_product]="MM"
And [tr_casetype] In
("C","P"),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate]),businessdays([tr_date_timercvd_hoi],[tr_closedate]))))
AS TimeToClose, IIf([tr_closedate] Is Null Or
[closeperiod]=0,Null,IIf([tr_expedited]=True,DateDiff("h",[tr_date_timercvd_hoi],[tr_closedate]),DateDiff("d",[tr_date_timercvd_hoi],[tr_closedate])))
AS TimeToCloseErisa, IIf([TR_Acknowltr] Is Null Or
[rcvdperiod]=0,Null,IIf([TR_Acknowltr] Is Not
Null,businessdays([tr_Date_timeRcvd_hoi],[tr_Acknowltr]),businessdays([tr_Date_timeRcvd_hoi],Date())))
AS AckCycle, IIf([tr_product]="HO" And [tr_expedited]=False And
[ackcycle]<=5,1,0) AS CommAckLetter, IIf([tr_product]="HO" And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS CommCasesRcvd,
IIf([tr_product]="HO" And [TR_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=15 And
[tr_expedited]=False,1,0) AS Comm15ERISA, IIf([tr_product]="HO" And
[tr_casetype] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Comm30ERISA,
IIf([tr_product]="HO" And [timetoclose]<=30 And [tr_extension]=False And
[tr_expedited]=False,1,0) AS Comm30, IIf([tr_product]="HO" And
[timetoclose]<=45 And [tr_extension]=True And [tr_expedited]=False,1,0) AS
Comm45, IIf([tr_product]="HO" And [TimeToCloseErisa]<=60 And
[tr_expedited]=False,1,0) AS Comm60, IIf([tr_product]="HO" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS CommCasesClosed,
IIf([tr_product]="HO" And [tr_expedited]=False And [tr_CaseType]="P" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [closeperiod]=1,1,0) AS
CommCasesClosed15ERISA, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_CaseType] In ("G","C") And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[closeperiod]=1,1,0) AS CommCasesClosed30ERISA, IIf([tr_product]="HO" And
[tr_expedited]=False And [tr_extension]=False And [closeperiod]=1,1,0) AS
CommCasesClosed30, IIf([tr_product]="HO" And [tr_expedited]=False And
[tr_extension]=True And [closeperiod]=1,1,0) AS CommCasesClosed45,
IIf([tr_expedited]=True And [tr_product]="HO" And [rcvdperiod]=1,1,0) AS
ExpeditedGrievCasesRcvd, IIf([tr_expedited]=True And [tr_product]="HO" And
[timetoclose]<=72,1,0) AS ExpeditedGriev, IIf([tr_expedited]=True And
[tr_product]="HO" And [closeperiod]=1,1,0) AS ExpeditedGrievCasesClosed,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[rcvdperiod]=1,1,0) AS ExpeditedCasesRcvd, IIf([tr_expedited]=True And
([tr_product]="MM" Or [tr_product]="AY") And [timetoclose]<=72,1,0) AS
Expedited, IIf([tr_expedited]=True And ([tr_product]="MM" Or
[tr_product]="AY") And [closeperiod]=1,1,0) AS ExpeditedCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [rcvdperiod]=1,1,0) AS MedicareGrievCasesRcvd,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_inquirytype]="GR" And
[ackcycle]<=5 And [tr_expedited]=False And [rcvdperiod]=1,1,0) AS
MedicareGrievAck, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [timetoclose]<=30 And [tr_expedited]=False,1,0)
AS
Medicare30G, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=30 And [tr_expedited]=False,1,0) AS Medicare30GERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[tr_inquirytype]="GR" And [closeperiod]=1,1,0) AS
MedicareGrievCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS MedicareGrievCasesClosedERISA,
IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="C" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicareClaimsCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="C" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicareClaimsAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[tr_expedited]=False And [closeperiod]=1,1,0) AS
MedicareClaimsCasesClosed,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="C" And
[timetoclose]<=60,1,0) AS Medicare60, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[rcvdperiod]=1,1,0) AS MedicarePreServCasesRcvd, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed, IIf(([tr_product]="MM"
Or
[tr_product]="AY") And [tr_casetype]="P" And [tr_expedited]=False And
[tr_extension]=False And [closeperiod]=1,1,0) AS
MedicarePreServCasesClosed30, IIf(([tr_product]="MM" Or [tr_product]="AY")
And [tr_casetype]="P" And [tr_expedited]=False And [tr_extension]=True And
[closeperiod]=1,1,0) AS MedicarePreServCasesClosed44,
IIf(([tr_product]="MM"
Or [tr_product]="AY") And [tr_casetype]="P" And [ackcycle]<=5 And
[tr_expedited]=False And [rcvdperiod]=1,1,0) AS MedicarePreServAck,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=30 And [tr_extension]=False And [tr_expedited]=False,1,0)
AS
Medicare30A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=44 And [tr_casetype]="P" And [tr_extension]=True And
[tr_expedited]=False,1,0) AS Medicare44, IIf([tr_inquirytype]="OD" And
[rcvdperiod]=1,1,0) AS OrgDetermRcvd, IIf([tr_inquirytype]="OD" And
[closeperiod]=1,1,0) AS OrgDetermClosed, IIf([tr_inquirytype]="OD" And
[timetoclose]<=72,1,0) AS OrgDeterm, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=72 And
[tr_expedited]=False,1,0) AS Medicare72GERISA, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_expedited]=False And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [tr_inquirytype]="GR" And
[closeperiod]=1,1,0) AS ExpeditedMedicareGrievCasesClosedERISA,
IIf(([tr_product]="MM" Or [tr_product]="AY") And [tr_casetype]="P" And
[timetoclose]<=44 And [tr_extension]=False And [tr_expedited]=False,1,0)
AS
Medicare44A, IIf(([tr_product]="MM" Or [tr_product]="AY") And
[tr_inquirytype]="GR" And [TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And
[timetocloseErisa]<=44 And [tr_expedited]=False,1,0) AS Medicare44GERISA,
IIf([tr_expedited]=True And ([tr_product]="MM" Or [tr_product]="AY") And
[timetoclose]<=72,1,0) AS Expedited72, IIf(([tr_product]="MM" Or
[tr_product]="AY") And [tr_inquirytype]="GR" And
[TR_DATE_TIMERCVD_HOI]>=#7/1/2002# And [timetocloseErisa]<=1 And
[tr_expedited]=False,1,0) AS Medicare1GERISA
FROM tblTrackingData LEFT JOIN tblExpedited ON tblTrackingData.ICNNO =
tblExpedited.ICNNO
WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
Click to expand...
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

I wouldn't do that, because you're allowing SQL to decide whether to band the
ANDs with the OR or not. The programmer has to force the issue. Hence,

WHERE tblTrackingData.TR_EXTENSION=True AND
((tblTrackingData.TR_INQUIRYTYPE<>"IN" And
tblTrackingData.TR_INQUIRYTYPE)<>"NC" AND
(tblTrackingData.TR_DATE_TIMERCVD_HOI Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR
(tblTrackingData.TR_CLOSEDATE
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))

This forces SQL to recognize the extension = true as a separate issue AND
either ((bunch A of requirements) OR (bunch B of requirements)).

HTW

John said:
Guessing, you might want something like the following. Note the addition of
the parentheses.

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
(
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND ((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
)

Stripping out the extra unneeded parens and consolidating everything

WHERE tblTrackingData.TR_EXTENSION=True AND
tblTrackingData.TR_INQUIRYTYPE<>"IN" And
tblTrackingData.TR_INQUIRYTYPE)<>"NC" AND

(tblTrackingData.TR_DATE_TIMERCVD_HOI Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate])
OR
tblTrackingData.TR_CLOSEDATE
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])
That's because you band all the And conditions before the OR (in the WHERE
statement) and you band separately all the And conditions after the OR. So
Click to expand...
[quoted text clipped - 168 lines]
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

No big argument, but that is the purpose of properly placed parentheses.
They "force" the SQL engine to properly band the ands and ors.

OfficeDev18 via AccessMonster.com said:
I wouldn't do that, because you're allowing SQL to decide whether to band
the
ANDs with the OR or not. The programmer has to force the issue. Hence,

WHERE tblTrackingData.TR_EXTENSION=True AND
((tblTrackingData.TR_INQUIRYTYPE<>"IN" And
tblTrackingData.TR_INQUIRYTYPE)<>"NC" AND
(tblTrackingData.TR_DATE_TIMERCVD_HOI Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
OR
(tblTrackingData.TR_CLOSEDATE
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))

This forces SQL to recognize the extension = true as a separate issue AND
either ((bunch A of requirements) OR (bunch B of requirements)).

HTW

John said:
Guessing, you might want something like the following. Note the addition
of
the parentheses.

WHERE (((tblTrackingData.TR_EXTENSION)=True) AND
(
((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between
[forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
OR (((tblTrackingData.TR_INQUIRYTYPE)<>"IN" And
(tblTrackingData.TR_INQUIRYTYPE)<>"NC") AND
((tblTrackingData.TR_CLOSEDATE)
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]))
)

Stripping out the extra unneeded parens and consolidating everything

WHERE tblTrackingData.TR_EXTENSION=True AND
tblTrackingData.TR_INQUIRYTYPE<>"IN" And
tblTrackingData.TR_INQUIRYTYPE)<>"NC" AND

(tblTrackingData.TR_DATE_TIMERCVD_HOI Between
[forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate])
OR
tblTrackingData.TR_CLOSEDATE
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate])
That's because you band all the And conditions before the OR (in the
WHERE
statement) and you band separately all the And conditions after the OR.
So
Click to expand...
[quoted text clipped - 168 lines]
Between [forms]![frmreports].[txtstartdate] And
[forms]![frmreports].[txtenddate]));
Click to expand...
Click to expand...
Click to expand...
 

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

nearest date 1
Date Format 5
Two PCs Different Results 10
Dialog in MsgBox 4
still no dynamic crosstab report 0
Date Question, not sure which forum 2

Top