Need to expand query or create a new ??

[aldeb]

The below sql query is working just fine for what it was
orginally created for. It is based on a date range that is
selected and it shows the ECN Number for the Planned
Implementation Date and Revised Planned
Implementation Date if either falls within the date
range selected. One of the ECN Analysts here has requested
some new functionality and I do not know how to provide it.

What is being requested is as follows:

If a ECN has a Planned Implementation Date that falls
within the date range and a Revised Implementation Date
that is outside of the date range then they do not want
the ECN to show up at all.

Since both dates are based on the date range I do not know
how to change the query or create a new one to accomplish
this. Since the query is specific to a date range for both
the date fields how can I change this query to accomplish
what is being requested?

All help is appreciated.

SELECT 
     ECNBCNVIPtbl.[ECN Analyst], 
     ECNBCNVIPtbl.[ECN Number], 
     ECNDetailtbl.[ECN Description], 
     ECNDetailtbl.[Planned Implementation Date], 
     "" as [Revised Planned Implementation Date], 
     ECNBCNVIPtbl.[Serial Number Break Required?], 
     ECNBCNVIPtbl.[Implementation Reporting Required?], 
     ECNBCNVIPtbl.[Do Not Process]
FROM 
     ECNBCNVIPtbl 
INNER JOIN 
     ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE 
     (((ECNBCNVIPtbl.[ECN Number])<>"sample") 
     AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] 
     And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) 
     AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
UNION SELECT 
     ECNBCNVIPtbl.[ECN Analyst], 
     ECNBCNVIPtbl.[ECN Number], 
     ECNDetailtbl.[ECN Description], 
     "", 
     ECNDetailtbl.[Revised Planned Implementation Date], 
     ECNBCNVIPtbl.[Serial Number Break Required?], 
     ECNBCNVIPtbl.[Implementation Reporting Required?], 
     ECNBCNVIPtbl.[Do Not Process]
FROM 
     ECNBCNVIPtbl 
INNER JOIN 
     ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") 
     AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) 
     AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];

 

[Pat Hartman]

If I understand the requirement, you will need to change the second query to repeat the first query so you can join to it in order to compare the two dates to be sure that both are within the time frame.

 

[aldeb]

Pat,

Thanks,

What I am needing is:

If the Planned Implementation Date is in the Daterange but it has
a Revised Planned Implementation Date that is not in the range I
need to see the Revised Planned Implementation Date for that ECN
especially if it is not in the date range.