Access 2007 VBA - Display conditional values passed into a report on the report

I've got a report that I'm generating dynamically from code like this...

DoCmd.OpenReport "rptExpenditureReport", acViewReport, , "[ProjectID]=" & CStr(lstbxProject.Value) & " AND [Date] BETWEEN #" & CStr(txtStartDate) & "# AND #" & CStr(txtEndDate) & "#"

So I'm passing in a date range to the report.  I need to be able to show that date range in the header of the report.  I can't use the min and max values of the data items returned because that might not be an accurate representation of the data if no data items fall on the dates used in the date range.

What are my options?

Who is Participating?
VTKeganConnect With a Mentor Commented:
You can use the OpenArgs portion of the DoCmd.OpenReport Method.

Then in the OnLoad event of the report, you can set your header to those values.

DoCmd.OpenReport "rptExpenditureReport", acViewReport, , "[ProjectID]=" & CStr(lstbxProject.Value) & " AND [Date] BETWEEN #" & CStr(txtStartDate) & "# AND #" & CStr(txtEndDate) & "#",,CStr(txtStartDate) & ";" & CStr(txtEndDate)

Private Sub Report_Load()
Dim DateArray() as string

If IsNull(Me.OpenArgs) Then
Exit Sub
DateArray = Split(Me.OpenArgs,";")
Me.txtHeaderStartDate = Date(0)
Me.txtHeaderEndDate = Date(1)
End If
End Sub
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Since it appear you are collection the data on a form then you can use a form reference this:

For a  a text box on a report set the control source to be:

= "Between " & Format(forms!MyForm.txtStartDate,"mm/dd/yyyy") & " and " &  Format(forms!MyForm.txtEndDate,"mm/dd/yyyy")

Where MyForm is the name of your form


You do not need to use CStr() for the txtStartDate.

You can use:

DoCmd.OpenReport "rptExpenditureReport", acViewReport, , "[ProjectID]=" & Val(Me.lstbxProject.Value) & " AND [Date] BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

Also Date is a reserved word and should  be avoided as field name or object names.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.