• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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?

1 Solution
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.

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now