Query based on date criteria filter 1

[netrusher]

Below is the SQL I have on a Union Query. Each Query is
based on a date range. The first Query date is Planned
Immplementation Date. The second Query date is Revised
Planned Implementation Date. If the Date in the Revised
Planned Implementation Date is higher than the EndDateTxt
Date Range I do not want the results to appear for that
ECN. Any suggestions on how to accomplish this?

SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], 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.[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]
UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], 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]

If you just fold the criteria for "Revised Planned Implementation Date" into the first WHERE clause then I don't think that you need the UNION because you are working with the same tables and fields in both sub-queries.

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 ((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,

Thanks, I tried your code and I only get one ECN Record.
I am trying to get all records with Planned Implementation
Date and Revised Planned Implementation Date in a two week
range. The only thing I do not want to see is if the
Revsied Planned Implementation Date is greater than the
EndDateTxt.

 

[netrusher]

I am using the date range of 04/21/08 thru 05/02/08. There
are 14 ECN's in that date range on the Planned Implementation
Date and 5 of those have a Revised Planned Implementation
Date higher than the 05/02/08 date. I would like for those
5 to not show up.

 

[Golom]

If you are getting only one record when you are expecting nine then I infer that eight of those nine are on dates earlier that the start date that you are using (i.e. 04/21/08).

If so, maybe you want

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

  [blue]AND ((D.[Revised Planned Implementation Date]) <=
          [Forms]![EcnVisualStatusFRM]![EndDateTxt])[/blue]

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

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

 

[netrusher]

Golom,

The table has been updated. What I am getting at this time
is 21 Records Planned Implementation Date between
04/21/08 & 05/02/08. Of those 21 records 5 have
Revised Implementation Dates greater than the 05/02/08 date.

 

[Golom]

Really strange netrusher.

I can see how your original SQL would do that but the stuff I posted should specifically exclude any record where "Revised Planned Implementation Date" is after [Forms]![EcnVisualStatusFRM]![EndDateTxt].

Are you running the SQL that I posted?

 

[PHV]

I'd replace this:
AND ((D.[Revised Planned Implementation Date]) <=
[Forms]![EcnVisualStatusFRM]![EndDateTxt])

with this:
AND ((Nz(D.[Revised Planned Implementation Date],D.[Planned Implementation Date])) <=
[Forms]![EcnVisualStatusFRM]![EndDateTxt])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Golom,

I run the below code and get two records.

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 ((D.[Revised Planned Implementation Date]) <=
          [Forms]![EcnVisualStatusFRM]![EndDateTxt])

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

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

 

[netrusher]

PHV,

When I run the below code I get no records at all.

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 ((Nz(D.[Revised Planned Implementation Date],D.[Planned Implementation Date])) <=
          [Forms]![EcnVisualStatusFRM]![EndDateTxt])  

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

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

 

[Golom]

How are you determining that there are (or should be) 16 records that qualify?

You said that there were 21 records with "[Planned Implementation Date]" between 04/21/08 & 05/02/08 of which 5 had "[Revised Planned Implementation Date]" after 05/02/08.

Both of those fields are in table ECNDetailtbl As D. Do they ALSO ...

- Have a matching value for "[ECNBCNVIP ID]" in the other table?
- Have the field [ECN Number] <> "sample" in the other table?
- Have the field [Do Not Process] = "yes" in the other table?

If you are just looking at ECNDetailtbl to determine how many there are you may be missing the other constraints in your WHERE clause.

 

[netrusher]

I have using the first half of the Union Query. That will
give me the records that have a Planned Implementation date
within the date range. Now there are 25 records within
my daterange with the Planned implementation date. The
date fields are in the detail table. The ECN data is in
the other table.

 

[Golom]

Just grasping at straws ...

Is [Revised Planned Implementation Date] a real datetime field or is it a text field that just looks like a date?

 

[Golom]

More grasping ...

Let's try hard coding the dates (just as a test.) There may be some issue with converting the values from your form.

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 #04/21/08# And #05/02/08# 

  AND ((D.[Revised Planned Implementation Date]) <=
          #05/02/08#)

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

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

 

[netrusher]

Everyone:

I got the code below and it is working for my Query. Thanks
for your help.

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

 

[PHV]

And what about this ?

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])
  OR (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];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

PH,

When I use your code it still gives me the Planned
Implementation Dates outside the date range to match
the Revised Planned Implementation Date.