Beginning Date Ending Date in parameter

[Emma]

Hi I'm trying to find a list of clients between [Beginning Date] and [Ending
Date] however when I run the report which has two charts, MS Access keeps
asking me for the dates over and over until I go through all of the clients,
(I assume). Is there anyway to ask for the dates just once?
I'm using Between [Beginning Date] And [Ending Date] as the criteria, I
tried using parameters but I don't know how to use a parameter as criteria?
Any advice would be helpful, Thanks

 

[fredg]

Hi I'm trying to find a list of clients between [Beginning Date] and [Ending
Date] however when I run the report which has two charts, MS Access keeps
asking me for the dates over and over until I go through all of the clients,
(I assume). Is there anyway to ask for the dates just once?
I'm using Between [Beginning Date] And [Ending Date] as the criteria, I
tried using parameters but I don't know how to use a parameter as criteria?
Any advice would be helpful, Thanks

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report and one also for each graph.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume all you need is a starting and ending date range.

Next, make a new unbound form.

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. Note: If you are using more than one query in
this report (as it seems you are)
As Criteria on the DateField for each query used (report and graphs)
write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Enter the starting and ending dates.
Click the command button.

The Report will display just those records within the date range.
When the Report closes it will close the form.

 

[Emma]

Hi Fred,

As it turns out I had a spelling mistake. However the next part of my
question is that I want to display the dates on the report ie From Dec 31,
1996 to Dec 31, 2008 but it's showing up in 12/31/1996 format I tried
applying med Date format but that didn't do anything, is this even possible?

Hi I'm trying to find a list of clients between [Beginning Date] and [Ending
Date] however when I run the report which has two charts, MS Access keeps
asking me for the dates over and over until I go through all of the clients,
(I assume). Is there anyway to ask for the dates just once?
I'm using Between [Beginning Date] And [Ending Date] as the criteria, I
tried using parameters but I don't know how to use a parameter as criteria?
Any advice would be helpful, Thanks

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report and one also for each graph.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume all you need is a starting and ending date range.

Next, make a new unbound form.

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. Note: If you are using more than one query in
this report (as it seems you are)
As Criteria on the DateField for each query used (report and graphs)
write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Enter the starting and ending dates.
Click the command button.

The Report will display just those records within the date range.
When the Report closes it will close the form.

 

[fredg]

Hi Fred,

As it turns out I had a spelling mistake. However the next part of my
question is that I want to display the dates on the report ie From Dec 31,
1996 to Dec 31, 2008 but it's showing up in 12/31/1996 format I tried
applying med Date format but that didn't do anything, is this even possible?

Hi I'm trying to find a list of clients between [Beginning Date] and [Ending
Date] however when I run the report which has two charts, MS Access keeps
asking me for the dates over and over until I go through all of the clients,
(I assume). Is there anyway to ask for the dates just once?
I'm using Between [Beginning Date] And [Ending Date] as the criteria, I
tried using parameters but I don't know how to use a parameter as criteria?
Any advice would be helpful, Thanks

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report and one also for each graph.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume all you need is a starting and ending date range.

Next, make a new unbound form.

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. Note: If you are using more than one query in
this report (as it seems you are)
As Criteria on the DateField for each query used (report and graphs)
write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Enter the starting and ending dates.
Click the command button.

The Report will display just those records within the date range.
When the Report closes it will close the form.

So you are still using the [Beginning Date] and [Ending Date] query
prompts?
Using an unbound control in the report header:
="For sales from " & Format([Beginning Date],"mmm d, yyyy") & " to " &
Format([Ending Date],"mmm d, yyyy")

if you do use the form method, which is better, then:

="For sales from " & Format(Forms!ParamForm!StartDate,"mmm d, yyyy") &
" to " & Format(Forms!ParamForm!EndDate,"mmm d, yyyy")