Showing records based on status

[Sunflower]

Is it possible?…
I have a report of job listings that shows, DueDate, Status and
Requester.
I have it grouped by Requester and then on order of DueDate.
Currently the report shows all jobs…
What I need the report to show is:
If status is “Completed” than only show those jobs for last 30 days
If status is “OPEN” than show all those jobs.
The only way I can figure to get this is to have 2 subreports , one
with Open jobs and one with Completed jobs. However, this would group
the jobs by status and I lose the order by DueDate.

Hope that made sense!

Any and all help is much appreciated

 

[John Spencer (MVP)]

Sounds as if you need to modify the underlying query.

The SQL for that would look like:

SELECT DueDate, Status, Requestor
FROM (Name of Your Table]
WHERE Status = "Open"
OR (Status = "Completed" AND DueDate >= DateAdd("d",-30,Date()))

In Query design view
-- Add your table
-- Add the fields you want in the report
-- In the first criteria row under status enter
= "Open"
-- In the second criteria row under Status enter
= "Completed"
-- In the second criteria row under date enter

= DateAdd("d",-30,Date()))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

Is it possible?…
I have a report of job listings that shows, DueDate, Status and
Requester.
I have it grouped by Requester and then on order of DueDate.
Currently the report shows all jobs…
What I need the report to show is:
If status is “Completed” than only show those jobs for last 30 days
If status is “OPEN” than show all those jobs.
The only way I can figure to get this is to have 2 subreports , one
with Open jobs and one with Completed jobs. However, this would group
the jobs by status and I lose the order by DueDate.

If you don't want data to be in the report, then it should
be filtered out in the report's record source query. I
think you can use this kind of Where clause:

(DueDate > DateAdd("d", -30, Date()) And Status =
"Completed") OR (Status = "Open")

 

[Sunflower]

If you don't want data to be in the report, then it should
be filtered out in the report's record source query.  I
think you can use this kind of Where clause:

(DueDate > DateAdd("d", -30, Date()) And Status =
"Completed") OR (Status = "Open")

Worked like a charm! Thank you so much! You saved me hours of work!

 

[Sunflower]

Sounds as if you need to modify the underlying query.

The SQL for that would look like:

SELECT DueDate, Status, Requestor
FROM (Name of Your Table]
WHERE Status = "Open"
OR (Status = "Completed" AND DueDate >= DateAdd("d",-30,Date()))

In Query design view
-- Add your table
-- Add the fields you want in the report
-- In the first criteria row under status enter
    = "Open"
-- In the second criteria row under Status enter
    = "Completed"
-- In the second criteria row under date enter
    >= DateAdd("d",-30,Date()))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Is it possible?…
I have a report of job listings that shows, DueDate, Status and
Requester.
I have it grouped by Requester and then on order of DueDate.
Currently the report shows all jobs…
What I need the report to show is:
   If status is “Completed” than only show those jobs for last 30 days
   If status is “OPEN” than show all those jobs.
The only way I can figure to get this is to have 2 subreports , one
with Open jobs and one with Completed jobs.  However, this would group
the jobs by status and I lose the order by DueDate.

Hope that made sense!

Any and all help is much appreciated- Hide quoted text -

- Show quoted text -

Worked beautifully, saved me hours of work
Thanks so much