Listing date range on a report

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a report whose record source is a query that contains a date field.

I want to display at the top of the report the begin (MIN) and end (MAX)
dates that were returned by the query.

I thought the best way to do this would be to use a Domain Aggregate
function in a textbox on the page header. However, I cannot get the syntax
to work.

I think I need something like this in the control source of the text box:

=DMax([OrderDate],[qr_Orders])

Where the expression is equal to the date field name, the domain is the
query that is the recordsource for the report, and the criteria is blank.

But I cannot get this or variations of it to work.

Can someone help me out with the syntax? Or suggest a better approach?
 
I have a report whose record source is a query that contains a date field.

I want to display at the top of the report the begin (MIN) and end (MAX)
dates that were returned by the query.

I thought the best way to do this would be to use a Domain Aggregate
function in a textbox on the page header. However, I cannot get the syntax
to work.

I think I need something like this in the control source of the text box:

=DMax([OrderDate],[qr_Orders])

Where the expression is equal to the date field name, the domain is the
query that is the recordsource for the report, and the criteria is blank.

But I cannot get this or variations of it to work.

Can someone help me out with the syntax? Or suggest a better approach?
Click to expand...

Aren't you entering the dates wanted into the query by using a
parameter? Something like
Between [Starting Date] and [Ending Date]

If so, add an unbound control to the Report Header.
Set it's control source to something like:
="For sales between " & [Starting Date] & " and " & [Ending Date]

The text within the brackets must be identical to the bracketed text
in the query.
 
Thanks Fred

The report is generated from a form with the following code in the click
event of a command button:

strSQL = "[OrderDate] >= " _
& " #" & txtBegDate.Value & "#" _
& " AND [OrderDate] <= " _
& " #" & txtEndDate.Value & "#"

'Handle bad input
If IsNull(strSQL) Then
MsgBox "No criteria specified"
Exit Sub
ElseIf txtBegDate.Value > txtEndDate.Value Then
MsgBox "Begin date cannot be greater than end date."
txtBegDate.SetFocus
Exit Sub
Else
DoCmd.OpenReport strDocName, acPreview, , strSQL


So there are no specified parameters as far as the report is concerned. I
think I need to query the record source directly to get the min and max
dates.

Or I suppose I could combine both dates in a string and pass them using the
OpenArgs parameter of the OpenReport command and then extract them in the
report. But that seems like a hack.

The DMax and DMin seemed like the easiest approach. I just can't work out
the syatax.
 
Thanks Fred

The report is generated from a form with the following code in the click
event of a command button:

strSQL = "[OrderDate] >= " _
& " #" & txtBegDate.Value & "#" _
& " AND [OrderDate] <= " _
& " #" & txtEndDate.Value & "#"

'Handle bad input
If IsNull(strSQL) Then
MsgBox "No criteria specified"
Exit Sub
ElseIf txtBegDate.Value > txtEndDate.Value Then
MsgBox "Begin date cannot be greater than end date."
txtBegDate.SetFocus
Exit Sub
Else
DoCmd.OpenReport strDocName, acPreview, , strSQL

So there are no specified parameters as far as the report is concerned. I
think I need to query the record source directly to get the min and max
dates.

Or I suppose I could combine both dates in a string and pass them using the
OpenArgs parameter of the OpenReport command and then extract them in the
report. But that seems like a hack.

The DMax and DMin seemed like the easiest approach. I just can't work out
the syatax.
Aren't you entering the dates wanted into the query by using a
parameter? Something like
Between [Starting Date] and [Ending Date]

If so, add an unbound control to the Report Header.
Set it's control source to something like:
="For sales between " & [Starting Date] & " and " & [Ending Date]

The text within the brackets must be identical to the bracketed text
in the query.
Click to expand...
Click to expand...

Use:
= "For Sales between " & forms!NameOfYourForm!txtBeginDate & " and " &
forms!NameOfYourForm!txtEndDate

The form must still be open when the report is run.
Change NameOfYourForm to the actual name of the form.

Note: The value property is the default property so it is not
necessary to state it, i.e. txtEndDate is the same as
txtEndDate.Value.
 
Thanks Fred.

That works but I was afraid of not having the form open when the report ran.

I ended up passing the dates as part of OpenArgs as:

DoCmd.OpenReport strDocName, acPreview, , strSQL, , txtBegDate & ";" &
txtEndDate

I then extracted the dates in the Report Activate event as:

Private Sub Report_Activate()

On Error GoTo ErrorHandler

If Not IsNull(Me.OpenArgs) Then
'Extract date range

Dim str As String 'passed arguments
Dim l As Integer 'length of passed string
Dim s As Integer 'positon of separator
Dim bd As String 'begin date
Dim ed As String 'end date

str = Me.OpenArgs

s = InStr(1, str, ";")
l = Len(str)
bd = (Left(str, s - 1))
ed = (Right(str, l - s))

txtDateRange = "All checks from: " & bd & " to: " & ed

Else
txtDateRange = "All checks displayed"
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description

End Sub


This seems to work well from what I've tested so far.

Just out of curiosity, I still would be interested in knowing:

1.How to use a Domain Aggregate function to read the Min/Max value in the
recordset that serves as a report's record source.
(e.g, something like: =DMax([OrderDate],[qr_Orders]))

2. How to test if a particular form is open.
 
Thanks Fred.

That works but I was afraid of not having the form open when the report ran.

I ended up passing the dates as part of OpenArgs as:

DoCmd.OpenReport strDocName, acPreview, , strSQL, , txtBegDate & ";" &
txtEndDate

I then extracted the dates in the Report Activate event as:

Private Sub Report_Activate()

On Error GoTo ErrorHandler

If Not IsNull(Me.OpenArgs) Then
'Extract date range

Dim str As String 'passed arguments
Dim l As Integer 'length of passed string
Dim s As Integer 'positon of separator
Dim bd As String 'begin date
Dim ed As String 'end date

str = Me.OpenArgs

s = InStr(1, str, ";")
l = Len(str)
bd = (Left(str, s - 1))
ed = (Right(str, l - s))

txtDateRange = "All checks from: " & bd & " to: " & ed

Else
txtDateRange = "All checks displayed"
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description

End Sub

This seems to work well from what I've tested so far.

Just out of curiosity, I still would be interested in knowing:

1.How to use a Domain Aggregate function to read the Min/Max value in the
recordset that serves as a report's record source.
(e.g, something like: =DMax([OrderDate],[qr_Orders]))

2. How to test if a particular form is open.
Click to expand...

Dave,
You've gone to an awful lot of work to do a very simple thing.

The problem with using the report's Activate event is that the
Activate event fires only when the report is previewed, not when
printed directly without preview.

Here is the usual method of passing a form control value as a
parameter to a query (and to the report header) which is being used as
the record source for a report.

I'll use a query that just asks for a range of dates, but it could
also include a company name, or product, etc.

Create an unbound form.
Add 2 text controls.
Name one txtStartDate.
The other txtEndDate.
Set their format property to Short Date.
Add a command button.
Code it's Click event:
Me.Visible = False
Name this form 'ParamForm'.

As Criteria in the query, write, on the DateField criteria line:
Between forms!ParamForm!txtStartDate and forms!ParamForm!txtEndDate

Next code the Report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

Add an unbound control to the Report Header to display the dates.
Set it's Control Source to:
"Some text " & forms!ParamForm!txtStartDate & " and " &
forms!ParamForm!txtEndDate

If you wanted to format the dates, when displayed in the report,
differently than when input in the form, you could use:
="Some text " & format(forms!ParamForm!txtStartDate,"mmmm d, yyyy") &
etc.
to show it as "Some text June 20, 2004" etc. instead of "Some text
6/20/2004"

When you are ready to run the report, open the Report.
The report will open the form. enter the date range wanted. Click the
command button. The report will display. The date range will appear in
the header. When you close the report, it will close the form.
 
Thank you very much Fred for taking time to explain that in detail.

Tying the parameter dialog to the report is a much cleaner approach and
allows me to treat all of my reports the same, whether they have parameters
or not.

Once again, thank you for enlightening me.

For those who may be following this thread, there is one additional step
that needs to be taken: set the record source of the report based on the
parameters. However, as explained earlier, once you know how to pass and
and access the parameters, setting the record source is realtively easy.
Using Fred's passing technique, I did it like this:

Private Sub Report_Open(Cancel As Integer)

On Error GoTo ErrorHandler

DoCmd.OpenForm "ParamForm", , , , , acDialog

Dim rpt As Report
Dim sql As String
Dim bd As String
Dim ed As String

bd = Forms!ParamForm!txtBeginDate
ed = Forms!ParamForm!txtEndDate

Set rpt = Reports("r_summary by category TEST")

If Not IsNull(bd) And Not IsNull(ed) Then

sql = "SELECT Investment.Investment, " & _
"Sum(Nz([qr_SummaryByCategory_Amount_JOIN.SplitAmount],0)) AS Amount
" & _
"FROM Investment LEFT JOIN qr_SummaryByCategory_Amount_JOIN ON
Investment.InvestID=qr_SummaryByCategory_Amount_JOIN.InvestID " & _
"WHERE qr_SummaryByCategory_Amount_JOIN.CheckDate Between #" & bd &
"# AND #" & ed & "# Or qr_SummaryByCategory_Amount_JOIN.CheckDate Is Null "
& _
"GROUP BY Investment.Investment " & _
"ORDER BY Investment.Investment;"

Else

sql = "SELECT Investment.Investment, " & _
"Sum(Nz([qr_SummaryByCategory_Amount_JOIN.SplitAmount],0)) AS Amount
" & _
"FROM Investment LEFT JOIN qr_SummaryByCategory_Amount_JOIN ON
Investment.InvestID=qr_SummaryByCategory_Amount_JOIN.InvestID " & _
"GROUP BY Investment.Investment " & _
"ORDER BY Investment.Investment;"

End If


rpt.RecordSource = sql


ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description
Resume ErrorHandlerExit:
End Sub
 
One small detail: if the user attempts to close the date selection form,
the report continues to load and throws an error.

I added the following code to the report's onOpen event to cancel if the
date selection form had been closed:

Dim i As Integer
Dim c As Integer
Dim b As Boolean

c = Forms.Count - 1

For i = c To 0 Step -1
If Forms(i).Name = "f_date_range" Then
b = True
End If
Next

If Not b Then
Cancel = True
Exit Sub
End If
 
Back
Top