group by query

  • Thread starter Thread starter gls858
  • Start date Start date
G

gls858

I have a group by query that has a field InvoiceDate

I have the criteria of Between [Start Date] and [End Date] to prompt for
the date range.

The Total parameter is set to Where.

I have the report that I want but I would like to pull this date range
as a field in my report header. Something like

My Report
Date 06/01/09 - 06/30/09

How do I add this date range to the report header?

gls858
 
One approach (klunky kludge) would be to prompt in the query, then prompt
AGAIN in the report. Nope, I don't like it either!

Another (more work, but less work each time you run the report) approach is
to create a "report order form". In that form, add two unbound textboxes.
Enter the Start and End date values in those two. Add a control button that
opens the report.

Modify your query to point to the form to get the parameters. Of course,
your form has to be open and the controls filled for this approach to work.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Add the prompts to the query, then use those fields in your report.

SELECT Field1, Field2, Field3, TransDate, [Start Date], [End Date]
FROM MyTable
WHERE TransDate BETWEEN [Start Date] AND [End Date]

Now, in your report, you can refer to Me![Start Date] and Me![End Date]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jeff Boyce said:
One approach (klunky kludge) would be to prompt in the query, then prompt
AGAIN in the report. Nope, I don't like it either!

Another (more work, but less work each time you run the report) approach
is to create a "report order form". In that form, add two unbound
textboxes. Enter the Start and End date values in those two. Add a
control button that opens the report.

Modify your query to point to the form to get the parameters. Of course,
your form has to be open and the controls filled for this approach to
work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

gls858 said:
I have a group by query that has a field InvoiceDate

I have the criteria of Between [Start Date] and [End Date] to prompt for
the date range.

The Total parameter is set to Where.

I have the report that I want but I would like to pull this date range as
a field in my report header. Something like

My Report
Date 06/01/09 - 06/30/09

How do I add this date range to the report header?

gls858
Click to expand...
Click to expand...
 
Aha! I get it. If you add two new fields in your query, they'll be
available as fields in your report!

Slick!

Jeff

Douglas J. Steele said:
Add the prompts to the query, then use those fields in your report.

SELECT Field1, Field2, Field3, TransDate, [Start Date], [End Date]
FROM MyTable
WHERE TransDate BETWEEN [Start Date] AND [End Date]

Now, in your report, you can refer to Me![Start Date] and Me![End Date]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jeff Boyce said:
One approach (klunky kludge) would be to prompt in the query, then prompt
AGAIN in the report. Nope, I don't like it either!

Another (more work, but less work each time you run the report) approach
is to create a "report order form". In that form, add two unbound
textboxes. Enter the Start and End date values in those two. Add a
control button that opens the report.

Modify your query to point to the form to get the parameters. Of course,
your form has to be open and the controls filled for this approach to
work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

gls858 said:
I have a group by query that has a field InvoiceDate

I have the criteria of Between [Start Date] and [End Date] to prompt for
the date range.

The Total parameter is set to Where.

I have the report that I want but I would like to pull this date range
as a field in my report header. Something like

My Report
Date 06/01/09 - 06/30/09

How do I add this date range to the report header?

gls858
Click to expand...
Click to expand...
Click to expand...
 
Back
Top