Same Parameters in all sub reports

  • Thread starter Thread starter Steph
  • Start date Start date
S

Steph

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
 
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.

Jeff Boyce said:
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

Steph said:
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,
Click to expand...
Click to expand...
 
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

Steph said:
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.

Jeff Boyce said:
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

Steph said:
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,
Click to expand...
Click to expand...
Click to expand...
 
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]));

Jeff Boyce said:
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

Steph said:
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.

Jeff Boyce said:
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
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,
Click to expand...
Click to expand...
Click to expand...
 
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

Steph said:
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]));

Jeff Boyce said:
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

Steph said:
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

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



Jeff Boyce said:
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

Steph said:
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]));

Jeff Boyce said:
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.

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

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

Steph said:
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



Jeff Boyce said:
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

Steph said:
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.

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.

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

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


Jeff Boyce said:
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

Steph said:
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



Jeff Boyce said:
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
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.

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.

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

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

Steph said:
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.


Jeff Boyce said:
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

Steph said:
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...?"

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
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.

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.

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

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
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top