Adding a date parameter for report and subreport to existing 2

[jeffshex]

I have a report called "Project Status Hours" and subreport named "Staff Listing Without Matching Project Status". Basically the main report just shows the manager's name and how many hours each of their staff worked. The subreport is an unmatched query and shows the name(s) of people who have not submitted anything. Since there is no date range it just grabs everything, and the unmatched query obviously follows suit.
I read through a few of the other posts involving sub-reports and I guess I need someone to explain to me or walk me on through what I need to do.
Not sure if the form is the way to go or some VB in the report open event.

Any ideas?

 

[THWatson]

jeff
Providing you have a date field column in your query that populates the report, you could do the following...

1. Create an unbound form. Let's call it frmSelectDates
2. On that form, create two unbound text boxes. Let's call them txtStartDate and txtEndDate
3. Also on the form, put a command button that runs the report in either Preview or Print mode
4. In the date column in your query, put the following criteria expression

Between Forms!frmSelectDates!txtStartDate And Forms!frmSelectDates!txtEndDate

If you want the dates to show on your report, put the following in an unbound text box in the report header

="This report covers the time period from " & Forms!frmSelectDates!txtStartDate & " to " & Forms!frmSelectDates!txtEndDate

Tom

 

[jeffshex]

The Subreport though disregards any date range. I have tried the form before and the find unmatched query in the subreport lists things outside of the specified date range.
Is there anything I'm doing wrong?
Thanks,

Jeff

 

[THWatson]

Jeff
Is the subreport linked to the main report - by a RecordID or something similar?

Tom

 

[jeffshex]

No, I have no link that I am aware of. Should I use the employee name or manager name for that?
Thanks!
-Jeff

 

[THWatson]

Jeff
I suspect that is the problem. You need to link the main report and subreport for both to work together.

Tom

 

[jeffshex]

I'm assuming i'm doing this under the link child/master fields. The only field in my subreport is the employee name. So i figured since that is in my main report that would work, but now after the date range box comes up it is bringing up it's own parameter box asking for a name a couple of times.
If I use the Manager to Name relationship...nothing comes up at all on this field.
I could send that database if you wanted a look at it.
Thanks again!
(i'll keep tryin!)

 

[THWatson]

Jeff
Before you send the database, I have one suggestion.

Assuming that your unmatched query is based on the same table as the query for the main report, and that there is a date field in the table, can you open the Unmatched Query and add the date field, and then put the same criteria that I suggested before in that date column?

If that doesn't work, you can send me the database. But zip it first. I could look at it later in the day.

I can't post my e-mail address here, because of the possibility of unwanted mail, but you can interpret the following...
twatson"at"sentex"dot"net

Tom

 

[jeffshex]

There is no date in the subreport...should i add that?
Just in case here is the SQL for the main report's query:

SELECT [Project Status].Name, [Project Status].Date, [Staff Listing].Manager, [Project Status].SickHours, [Project Status].VacationHours, [Project Status].HolidayHours, [Project Status].ProjectHours, [Project Status].TechHours, [Project Status].AdminHours, [Project Status].AfterHoursMF, [Project Status].AfterHoursSat, [Project Status].AfterHoursSun, [Project Status].SubTotalRegHours, [Project Status].SubTotalAfterHours, [Project Status].SubTotalLeaveHours
FROM [Staff Listing] INNER JOIN [Project Status] ON [Staff Listing].Name = [Project Status].Name
WHERE ((([Project Status].Date) Between [Forms]![date range form]![StartDate] And [Forms]![date range form]![EndDate]));

--------------------------------------------------------
Here is the unmatched query

SELECT [Staff Listing].Name
FROM [Staff Listing] LEFT JOIN [Project Status] ON [Staff Listing].Name = [Project Status].Name
WHERE ((([Project Status].Name) Is Null));

I really appreciate all the help!

 

[THWatson]

Jeff
A couple of points...

I see that two of your fields in the [Project Status] table are "Name" and "Date." Both of those words are reserved words in Access and it's best to avoid using them, as Access can get confused. The most problematic of the two is "Date" as Date() always refers to today (whatever today is) and in VBA Access will truncate off the brackets so that Date() becomes Date.

I also see that you have a field in [Staff Listing] called "Name."

In any event, from looking at the two SQLs I'm not sure whether or not adding the Date field to the query for the subreport will do it. You can try it, but I'm not sure.

If you wish, sending the database to me is still an option. If you do that, please tell me exactly what you are trying to get out in the report.

Tom

 

[jeffshex]

The unmatched query just looks at the whole table. Somehow I need to have it look only at the results of the main reports query.

 

[THWatson]

Jeff
An idea.

1. Your query for the main report gives the names of people who have submitted stuff during the dates for which you desire to check. For sake of this example, I will call it qry 1.
2. Creaate another query that lists all of the names. For sake of this example I will call it qry2.
3. Create a third query (qryNamesUnmatched) - this one an Unmatched query that checks for the names in qry2 that do not exist in qry1.
4. Build your subreport on qryNamesUnmatched.

Would that work?

Tom

 

[jeffshex]

Should that 2nd query just be names and names only. Don't need manager name or anything?

 

[jeffshex]

Sorry....also, is this 2nd query looking at the staff listing table, or the table where everybody submits stuff?
Thanks!

-Jeff

 

[jeffshex]

That worked Tom! I just messed around with it until it worked. I just did a query on the staff listing table. Then on the unmatched query, I had it use that table for results that didn't appear in the main reports query.
That seemed pretty easy compared to all the different methods i've been trying.
Now the final question (it's an easy one). Is there any way to change the way that subreport appears. It looks like a table pasted right there with the column head, lines, and everything. (I didn't see anything in the subreport properties.)
Other than that...great thinking Tom!

-Jeff

 

[THWatson]

Jeff
Glad that is working.

As for the subreport appearance, you should be able to open it by itself, put in Headers or Footers, or do whatever you wish with it.

Tom

 

[jeffshex]

The weird thing is, since it is a query of a query...it just brings up the query in design view when I double click on it to edit.
No big deal though...they can deal with it.
Thanks for your help!

-Jeff

 

[THWatson]

Jeff
I don't know what to say about that. It isn't what happens at my end.

What shows as the Record Source for the subreport?

Tom

 

[jeffshex]

I got it worked out. I didn't even have a report for that unmatched query. I just stuck the query in the main report, that's why it was doing what it was. I just made a report from that query and popped it in and everything works great now!
Thanks again.
-Jeff