MS Access Report-CodePudding

Is there a way to use the user's input (start and end) dates in a report text box? For example, I have an Access query that will prompt the user for a start date and end date. I then used that query to run a report for just that date range. But I would like to use the start and stop date that the user enters, in the report title. Can anyone help with this?

Thanks,

CodePudding user response：

You can assign the values you want to use to global variables and then use the Report_Load() event sub to place those values as the report loads.

Global assignments must be done in a module, you can't do it from the report or form's code-behind. I've named my module GlobalAssignments.

Global startdate As String
Global enddate As String

Sub SetStartDate(val As Date)
    startdate = CStr(val)
End Sub

Sub SetEndDate(val As Date)
    enddate = CStr(val)
End Sub

In the form you're using to get the dates from the user, something like this:

Private Sub cmd_RunReport_Click()
    Dim startdate As Date
    Dim enddate As Date
    With Me
        startdate = .Txt_StartDate.Value
        enddate = .txt_EndDate.Value
    End With
    
    GlobalAssignments.SetStartDate startdate
    GlobalAssignments.SetEndDate enddate
    DoCmd.OpenReport "Rpt_DateRange", acViewPreview 'Pretty sure it has to be preview
End Sub

And in the Report:

Private Sub Report_Load()
    Me.lbl_Header.Caption = "Date Report for range: " & startdate & " - " & enddate
End Sub

CodePudding user response：

If you're already using the startdate and enddate parameters in the query's criteria - just add the parameters to the Field list. Then you can simply include them in the text box ControlSource

Say your query is

SELECT myTable.* FROM myTable WHERE myTable.myDateField BETWEEN [Start Date] and [EndDate]

Change it to

SELECT myTable.*, ReportStartDate:[Start Date], ReportEndDate:[EndDate] 
FROM myTable WHERE myTable.myDateField BETWEEN [Start Date] and [End Date]

Then, assuming your Report Data Source is set to your query or to SELECT * FROM yourquery

You simply change your Textfield ControlSource to concatenate your query parameter names

="Between " & [ReportStartDate] & " AND " & [
SELECT myTable.*, ReportStartDate:[Start Date], ReportEndDate]