Link to home
Start Free TrialLog in
Avatar of nap0leon
nap0leon

asked on

Limiting report data

Follow up to this question.

I want to change this query:
SELECT *
FROM [Project List] LEFT JOIN (SELECT * FROM Comments WHERE Comments.[Comment Date]>DateAdd("d",-14,date()))  AS q ON [Project List].ID=q.[Project ID]
WHERE ((([Project List].Resource)=[ResourceFilter]));

Open in new window


So that it does not return projects that are "completed" with a completion date > 14 days ago.

Relevant fields:
[Project List].[Closed Date]
[Project List].[Status]

Open in new window


The value for "Status" for completed projects is "99 Complete"
Avatar of Barry62
Barry62
Flag of United States of America image

SELECT *
FROM [Project List] LEFT JOIN (SELECT * FROM Comments WHERE Comments.[Comment Date]>DateAdd("d",-14,date()))  AS q ON [Project List].ID=q.[Project ID]
WHERE [Project List].[Status] = "99 Complete" and ([Project List].[Closed Date] < DATE_SUB(date,INTERVAL 14 DAY)) and ((([Project List].Resource)=[ResourceFilter]));

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of nap0leon
nap0leon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nap0leon
nap0leon

ASKER

I've requested that this question be deleted for the following reason:

Solved it myself
Although my syntax was a bit off, I did provide you with the mysql function (date_sub) which inspired your fix.
Actually no.  

I had already decided that I did not need to look at "Status" and would instead simply filter on "Closed Date".

For the Projects Filter, I am using the DateAdd (the same as I am already using in the Comments filter section) and a test that allows NULL.

Besides... You had it backwards (showing only stuff that was closed in the past two weeks rather than showing everything except what was closed over 2 weeks ago.

Thank you for trying, but, honestly, I used absolutely nothing from your solution.
Fair enough.  I tried.  I won't object to you closing the question.
Solved it myself