Solved

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

Posted on 2011-02-11
2
258 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:JosephEricDavis
2 Comments
 
LVL 10

Accepted Solution

by:
VTKegan earned 500 total points
ID: 34873601
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
Else
DateArray = Split(Me.OpenArgs,";")
Me.txtHeaderStartDate = Date(0)
Me.txtHeaderEndDate = Date(1)
End If
End Sub
0
 
LVL 21
ID: 34873775
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now