 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / April 2008Same Parameters in all sub reports
I have a main report and 12 unbound subreports. They all use the same 2 parameters. [Month] & [Year]. I would like to only have to enter the month and year once and have it pass to all the queries. I tried to see if there was another posting that fit, but nothing seemed to be the same. Please let me know how I can do this. Thanks, Steph One way to do this would be to create a form that "gathers" the month and year criteria. Each query would need to be revised to "point to" that form and the controls holding the criteria. By the way, if your table fields are actually named [Month] and [Year], consider using a more descriptive title -- these two are reserved words in Access and may cause you (and Access) considerable confusion! Regards Jeff Boyce Microsoft Office/Access MVP >I have a main report and 12 unbound subreports. They all use the same 2 > parameters. [Month] & [Year]. I would like to only have to enter the month [quoted text clipped - 4 lines] > > Thanks, Ok, I know how to create a form, but how do I get my form to work for my report? I actually can use the same criteria for 2 separate reports. Oh, and no, the names aren't just month and year. It's really [ReportMonth] and [ReportYear]. The queries aren't the problem. I'm just sick of seeing the parameters show up multiple times when they all need the same month entry and same year entry. > Steph > [quoted text clipped - 19 lines] > > > > Thanks, You don't "get your form to work for your report. You design queries that return the records you want. You use the form's controls to provide the queries' selection criteria. You base your reports on the queries. That way, when each report runs, it looks to its query... and when its query runs, IT looks to the form. Of course, this won't work if you don't have the form open and values entered. Good luck Regards Jeff Boyce Microsoft Office/Access MVP > Ok, I know how to create a form, but how do I get my form to work for my > report? I actually can use the same criteria for 2 separate reports. [quoted text clipped - 34 lines] >> > >> > Thanks, Maybe I didn't word it right, but how to I get it to work then? Please give me a real example. I will be the first to admit that I'm not the most well-versed with Access, but didn't seem to find anything in here that worked. Here's the SQL code of one of my queries: PARAMETERS [ReportMonth] Long, [ReportYear] Long; SELECT [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], [Time Entry Records].[Parent Request Type], Sum([Time Entry Records].Hours) AS TotalHours FROM [Time Entry Records] GROUP BY [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], [Time Entry Records].[Parent Request Type], [Time Entry Records].Date HAVING ((([Time Entry Records].[Associate's Org])="Penske Truck Leasing") And (([Time Entry Records].[Associate's Dept])="PTL - MIS") And (([Time Entry Records].[Associate's Team])<>"PTL MIS - GDC") And (([Time Entry Records].[Parent Request Type])="Support Break/Fix") And ((Month([time entry records].date))=[ReportMonth]) And ((Year([time entry records].date))=[ReportYear])); > You don't "get your form to work for your report. > [quoted text clipped - 53 lines] > >> > > >> > Thanks, I'm not following your question "how do I get it to work...?" Build a query that will be used to provide data to your report. In the query design window, under a field that you want to use a parameter for, in the Selection Criterion, put a reference to the form's control(s), something like: Forms!YourFormName!YourControlName Build your form and add the controls you'll need to "feed" the selection criteria for the query(ies). What am I missing? Regards Jeff Boyce Microsoft Office/Access MVP > Maybe I didn't word it right, but how to I get it to work then? Please > give [quoted text clipped - 91 lines] >> >> > >> >> > Thanks, Well, I hate to sound dumb, but I guess I have no clue what I'm doing here. Again, I have multiple queries. All my queries are indivually in a subreport. All the subreports are on one main report. I understand that I need to create a form and get it into my query, but I'm struggling with the process. Here's a sample of one of my queries. PARAMETERS [ReportMonth] Long, [ReportYear] Long; SELECT [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], Sum([Time Entry Records].Hours) AS TotalHours FROM [Time Entry Records] GROUP BY [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], [Time Entry Records].Date HAVING ((([Time Entry Records].[Associate's Org])="Penske Truck Leasing") AND (([Time Entry Records].[Associate's Dept])="PTL - MIS") AND (([Time Entry Records].[Associate's Team])="PTL MIS - GDC") AND ((Month([time entry records].[date]))=[ReportMonth]) AND ((Year([time entry records].[date]))=[ReportYear])); I'm pretty good at the queries, but not sure how I get the rest to flow. I understand that the form would have my parameters, but I can't seem to get it to work. Please let me know how I can get this working. Thanks, Steph > I'm not following your question "how do I get it to work...?" > [quoted text clipped - 111 lines] > >> >> > > >> >> > Thanks, Sorry I'm not of more help. In my previous response, I mentioned putting a reference to the value in the form into the query's Selection Criterion. Did you try that? Regards Jeff Boyce Microsoft Office/Access MVP > Well, I hate to sound dumb, but I guess I have no clue what I'm doing > here. [quoted text clipped - 159 lines] >> >> >> > >> >> >> > Thanks, Yes, I did. This is the test qry I've been using: SELECT [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], Sum([Time Entry Records].Hours) AS TotalHours FROM [Time Entry Records] GROUP BY [Time Entry Records].[Associate's Org], [Time Entry Records].[Associate's Dept], [Time Entry Records].[Associate's Team], [Time Entry Records].Date HAVING ((([Time Entry Records].[Associate's Org])="Penske Truck Leasing") And (([Time Entry Records].[Associate's Dept])="PTL - MIS") And (([Time Entry Records].[Associate's Team])="PTL MIS - GDC") And (((Month([time entry records].date)))=Forms!frm_mtd!ReportMonth) And (((Year([time entry records].date)))=Forms!frm_mtd!ReportYear)); I don't get any records from this, so I'm not sure what I'm doing wrong. I'm sure there's something between my form and the query that's right. I am using a linked excel spreadsheet. The column this is pulling from is general text. I'm not sure if that's going to matter. It didn't for my regular queries. I believe it's related to the form, but can't pinpoint that for sure. > Sorry I'm not of more help. > [quoted text clipped - 171 lines] > >> >> >> > > >> >> >> > Thanks, If this were mine, my next step would be to eliminate the reference to the forms' controls (temporarily) and use some test date values to see if the query is working correctly. If it returns the rows you expect, there's something wrong between the form and the query. If not, the query isn't right yet. I'd keep stripping out pieces of the query until it worked, then start rebuilding it until it breaks to isolate the problem. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP > Yes, I did. > [quoted text clipped - 225 lines] >> >> >> >> > >> >> >> >> > Thanks, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.