Putting user defined Dates into Reports

markizan · October 3rd, 2006, 07:57 PM

HI,

I have a query [CustomerOrdersByDate] that requests user input for [CustomerID], a 'Start Date' and an 'End Date' for the [OrderDate] field.

the Report works fine, however I'd like it to be able to take the Start & End dates the user has inputted when running the report, and make it appear in the Report header
e.g. Orders Received from [CustomerID], Between {user defined}[Enter Start Date] And {user defined}[Enter End Date].

I have added a text box to display the results and played around with a few different expressions, etc... unsuccessfully so far, and as I'm not up to working with VBA code, I would appreciate some help!

Bob Bedell · October 4th, 2006, 09:06 PM

Use the reports Open event to invoke your Date Range selection dialog (complete with CustomerID, Beginning Date, and Ending Date text boxes) with something like:

Code:

Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, "Set Date Range"
    If Not IsLoaded("frmReportDateRange") Then
        Cancel = True
    End If
End Sub

Then in the Control Source property of your textbox in your report header, place something like:

Code:

="Orders Recieverd from " & [Forms]![frmReportDateRange]![txtCustomerID] & â, â & âBetween â &   [Forms]![frmReportDateRange]![txtBeginningDate] & " And " & [Forms]![frmReportDateRange]![txtEndingDate]

Have your Date Range selection form open as a modal form, place a report preview command button on it, then hide the form (while keeping it loaded) when the the preview button is clicked with something like:

Code:

Private Sub cmdPreview_Click()

On Error GoTo HandleErr

    If IsNull([txtBeginningDate]) Or IsNull([txtEndingDate]) Then
        MsgBox "You must enter both beginning and ending dates."
        DoCmd.GoToControl "txtBeginning Date"
    Else
        If [txtBeginningDate] > [txtEndingDate] Then
            MsgBox "Ending date must be greater than Beginning date."
            DoCmd.GoToControl "txtBeginningDate"
        Else
            Me.Visible = False
        End If
    End If
ExitHere:
    Exit Sub

HandleErr:
    Select Case Err
        Case Else
            MsgBox Err & ": " & Err.Description, vbCritical, _
             "Error in Form_frmReportDateRange.Preview_Click"
    End Select
    Resume ExitHere
    Resume
End Sub

Might also want to add a NoData event to the report in case no data is available for the selected date range:

Code:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
End Sub

Private Sub Report_Close()
    DoCmd.Close acForm, "frmReportDateRange"
End Sub

And here's a generic IsLoaded routine called by the reports Open event:

Code:

Function IsLoaded(ByVal strFormName As String) As Integer
 ' Returns True if the specified form is open in Form view or Datasheet view.

    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If

End Function

Somethin' like that...

HTH,

Bob

Bob Bedell · October 4th, 2006, 09:12 PM

Quote:

quote:
...and as I'm not up to working with VBA code

Just noticed that bit. The heart of the matter is the expression:

="Orders Recieverd from " & [Forms]![frmReportDateRange]![txtCustomerID] & â, â & âBetween â & [Forms]![frmReportDateRange]![txtBeginningDate] & " And " & [Forms]![frmReportDateRange]![txtEndingDate]

Place it in a text box's Control Source property, then just be sure you have the form with txtCustomerID, txtBeginningDate, txtEndingDate loaded so the expression can reference the text property of those controls. May the force be with you...

Bob

Bob Bedell · October 4th, 2006, 09:35 PM

Also just dawned on me that you are trying to do this from a query. (Its late, long day). So have your query pick up its parameter values from the date range selection form also. Enter the following as criteria for your OrderDate field:

Code:

>=[Forms]![frmReportDateRange]![txtBeginningDate] And <=[Forms]![frmReportDateRange]![txtEndingDate]

and the following for the criteria for your CustomerID field:

Code:

[Forms]![frmReportDateRange]![txtCustomerID]

Then use the query as the record source of your form.

'Course the approach I'm driving at entails adding a date range selection dialog form to your app, and thats going to take a little VBA.

Bob