Need to expand query or a new one 1

[netrusher]

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];

 

[Golom]

Can you explain a bit?

It looks like this would produce two records for each [ECN Analyst], [ECN Number], [ECN Description], etc.

The first would have a value for [Planned Implementation Date] but nothing for [Revised Planned Implementation Date]. The second would have nothing for [Planned Implementation Date] would have data for [Revised Planned Implementation Date]. Otherwise the JOIN and the WHERE conditions are the same.

Would your purposes be served equally well if only one record was produced containing data in both fields?

If so

SELECT 
	E.[ECN Analyst], 
	E.[ECN Number], 
	D.[ECN Description], 
	D.[Planned Implementation Date], 
	D.[Revised Planned Implementation Date], 
	E.[Serial Number Break Required?], 
	E.[Implementation Reporting Required?], 
	E.[Do Not Process]

FROM ECNBCNVIPtbl As E INNER JOIN ECNDetailtbl As D ON E.[ECNBCNVIP ID] = D.[ECNBCNVIP ID]

WHERE E.[ECN Number])<>"sample" 

  AND D.[Planned Implementation Date] 
      Between [Forms]![EcnVisualStatusFRM]![StartDateTxt]
          And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) 

  AND [b][red] NOT [/red][/b](D.[Revised Planned Implementation Date] 
       Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] 
           And [Forms]![EcnVisualStatusFRM]![EndDateTxt])

  AND E.[Do Not Process]="yes"

ORDER BY E.[ECN Analyst], E.[ECN Number];

 

[netrusher]

Golom,

I used your code below. I had to remove a Parens but the
results I am getting is only giving me one record. Some
of the records will not contain data in both fields. If
there is a date in the Revised Planned Implementation Date
there will always be one in the Planned Implementation Date
but not vice versa. Also the dates in either field might
not be in the date range. Meaning: The date range I am
currently using is May19-May30.

I have 31 records that either have a Planned Date or Revised
date in the date range. My dilemma is I have one record
that has a Planned Date in the date range and it also has
a Revised date not in the date range. I need to see the
Revised date even though it is not in the date range. Hope
this makes sense.

SELECT 
    E.[ECN Analyst], 
    E.[ECN Number], 
    D.[ECN Description], 
    D.[Planned Implementation Date], 
    D.[Revised Planned Implementation Date], 
    E.[Serial Number Break Required?], 
    E.[Implementation Reporting Required?], 
    E.[Do Not Process]

FROM ECNBCNVIPtbl As E INNER JOIN ECNDetailtbl As D ON E.[ECNBCNVIP ID] = D.[ECNBCNVIP ID]

WHERE E.[ECN Number]<>"sample" 

  AND D.[Planned Implementation Date] 
      Between [Forms]![EcnVisualStatusFRM]![StartDateTxt]
          And [Forms]![EcnVisualStatusFRM]![EndDateTxt] 

  AND NOT (D.[Revised Planned Implementation Date] 
       Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] 
           And [Forms]![EcnVisualStatusFRM]![EndDateTxt])

  AND E.[Do Not Process]="yes"

ORDER BY E.[ECN Analyst], E.[ECN Number];

 

[Golom]

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

Planned Date in the date range and it also has a Revised date not in the date range. [blue]I need to see the Revised date even though it is not in the date range[/blue]

These seem to be contradictory requirements.

You don't them to appear (first post) or you do (second one)?

 

[netrusher]

Golom,

It is contradictory and that is why I said I might need
create a new query. I wanted to get input on any way
to accomplish what I need.