Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: Code Help for Date Range Report


    I used some code I found here: to try and run my report based on a date range.

    I have modified it only to customise the report name/field name. However, when I run this (with a date range of 01/01/2012 to 30/01/2012) I get an error with this line of code:

    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"

    Can anybody help me?

    My full code is:
    Private Sub cmdPreview_Click()
    'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
        'Purpose:       Filter a report to a date range.
        'Note:          Filter uses "less than the next day" in case the field has a time component.
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
        'DO set the values in the next 3 lines.
        strReport = "Basic Hours Report"      'Put your report name in these quotes.
        strDateField = "[DateofVisit]" 'Put your field name in the square brackets in these quotes.
        lngView = acViewPreview     'Use acViewNormal to print instead of preview.
        'Build the filter string.
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        End If
        'Close the report if already open: otherwise it won't filter properly.
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        'Open the report.
        'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
        DoCmd.OpenReport strReport, lngView, , strWhere
        Exit Sub
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub

  2. #2
    Join Date
    May 2012
    Try putting a space after the <

    It would help to give the actual error message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts