asked on

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?

thanks

ASKER CERTIFIED SOLUTION

VTKegan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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

Also:

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.