Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014
    Posts
    7

    Unanswered: Report on threee conditions

    Hi all
    i used the code below to open a report on certain date or on date range ... it works fine ....
    now i need to add more one condition from combobox called CMB_dt with value 1 or 2 or 3 to filter the report on these three fields
    thanks ...

    Code:
    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.
        'Documentation: http://allenbrowne.com/casu-08.html
        '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 = "rptSales"      'Put your report name in these quotes.
        strDateField = "[SaleDate]" '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_Handler:
        Exit Sub
    
    Err_Handler:
        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
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24

    no code needed

    You should not need to use any code. The query (the report is using) should have references to the form and cbo boxes as parameters.

    forms!frmMyform!cboBox1

    forms!frmMyform!cboBox2

    Zero programming. Wont this work for you?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by f.dragon View Post
    now i need to add more one condition from combobox called CMB_dt with value 1 or 2 or 3 to filter the report on these three fields
    thanks ...
    Which 3 fields? As it is, the code you posted build a criteria that filters on [SaleDate] with its value coming from txtStartDate. What are the names of the other 2 fields that should be added to the criteria? Moreover, if the value for a second criteria would come from CMB_dt, where would the value for the third criteria come from?

    Quote Originally Posted by ranman256 View Post
    forms!frmMyform!cboBox1

    forms!frmMyform!cboBox2

    Zero programming. Wont this work for you?
    @ranman256: No it would not work:

    1. Where do these 2 combos come from (only one combo: CMB_dt) is mentioned.
    2. What do you do with the existing criteria based on [SaleDate]?
    Have a nice day!

Posting Permissions

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