Showing records based on status

  • Thread starter Thread starter Sunflower
  • Start date Start date
S

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
 
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()))
Click to expand...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Sunflower said:
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.
Click to expand...


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")
 
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")
Click to expand...

Worked like a charm! Thank you so much! You saved me hours of work!
 
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.
Click to expand...
Hope that made sense!
Click to expand...
Any and all help is much appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Worked beautifully, saved me hours of work
Thanks so much
 
Back
Top