report & subreport date field

  • Thread starter Thread starter George
  • Start date Start date
G

George

Hi there,
I have one main report and one subreport.

In both queries i have in the date column the following criteria:
Between [Type Report Start Date (dd/mm/yyy):] And [Type End Date
(dd/mm/yyy):]

When i view the reports individually i am asked to enter the start and end
dates. But now that i have one as a sub report i have to type in the dates 2
times each. Is there a way to only type this once.

My next problem is that i have a text box on both reports that display the
start and end dates. The control source for these text boxes are:=[Type SMA
Report Start Date (dd/mm/yyy):] and for the end date it is =[Type SMA End
Date (dd/mm/yyy):].

And as you could imagine when i try to view the report i am asked for the
date 8 + times.

As you can appreciate i need urgent H.E.L.P here.

Thanks

George
 
1. Create a form, with 2 unbound text boxes where the user can enter the
dates.

2. Change the queries so they refer to the text boxes, e.g.:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

3. Change the reports to they refer to the text boxes too:
=[Forms].[Form1].[StartDate]

4. Open the report from the form.

To ensure this all goes smoothly:
a) Set the Format property of the text boxes to Short Date or similar, so
Access knows they are dates.

b) Declare the parameters in the query. Choose Parameters on Query menu (in
query design), and enter 2 rows in the dialog:
[Forms].[Form1].[StartDate] Date
[Forms].[Form1].[EndDate] Date

c) The command button on the form can check for the dates before it opens
the report. Example event procedure for the button's Click event:

Private Sub cmdPreview_Click()
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox "Both dates."
Else
DoCmd.OpenReport "Report1", acViewPreview
End If
End Sub
 
Thanks Allen,
that worked a treat. the only way to open is through the use of the form as
if i try to open the report from the reports section of access i get prompted
to enter the date 10 times again.

Cheers
George


Allen Browne said:
1. Create a form, with 2 unbound text boxes where the user can enter the
dates.

2. Change the queries so they refer to the text boxes, e.g.:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

3. Change the reports to they refer to the text boxes too:
=[Forms].[Form1].[StartDate]

4. Open the report from the form.

To ensure this all goes smoothly:
a) Set the Format property of the text boxes to Short Date or similar, so
Access knows they are dates.

b) Declare the parameters in the query. Choose Parameters on Query menu (in
query design), and enter 2 rows in the dialog:
[Forms].[Form1].[StartDate] Date
[Forms].[Form1].[EndDate] Date

c) The command button on the form can check for the dates before it opens
the report. Example event procedure for the button's Click event:

Private Sub cmdPreview_Click()
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox "Both dates."
Else
DoCmd.OpenReport "Report1", acViewPreview
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

George said:
Hi there,
I have one main report and one subreport.

In both queries i have in the date column the following criteria:
Between [Type Report Start Date (dd/mm/yyy):] And [Type End Date
(dd/mm/yyy):]

When i view the reports individually i am asked to enter the start and end
dates. But now that i have one as a sub report i have to type in the dates
2
times each. Is there a way to only type this once.

My next problem is that i have a text box on both reports that display the
start and end dates. The control source for these text boxes are:=[Type
SMA
Report Start Date (dd/mm/yyy):] and for the end date it is =[Type SMA End
Date (dd/mm/yyy):].

And as you could imagine when i try to view the report i am asked for the
date 8 + times.
Click to expand...
Click to expand...
 
Back
Top