Results 1 to 4 of 4
  1. #1
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22

    Angry Filter Report by Optional Date Range and Combo Box

    I would like to filter Report: DQC by an OPTIONAL date range (which I have done with the code below). I would also like to filter it by and OPTIONAL combo box that self populates from my table with PEBLO names. I have gotten the combo box to self populate, but do not know how to add it into the VB. Combo Box Name: Combo27

    Private Sub Command29_Click()


    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    strReport = "DQC"
    strDateField = "[Date]"
    lngView = acViewPreview

    If IsDate(Me.Text22) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.Text22, strcJetDate) & ")"
    End If
    If IsDate(Me.Text24) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.Text24 + 1, strcJetDate) & ")"
    End If


    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    '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
    Private Sub RUN_Click()
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can test whether the user has made a selection with:

    If Len(Me.Combo27 & vbNullString) > 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22
    pbaldy,
    I'm really new at this SQL. I know enough to adapt the above SQL posted by another user to my database, but I have absolutly no clue what you are talking about "test whether the user has made a selection". I want the selection to be optional. If no PEBLO is selected the report will run for all PEBLO's. I have the dates as optional and working, I just don't know how to add the combo box option (optional) to the existing SQL.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In the same way you tested whether the user entered a date and added to the string if they did, you want to test the combo and add to the string if they made a selection.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 04-29-2015, 11:57 PM
  2. Multiple Field & date range filter
    By mrkandohi001 in forum Reports
    Replies: 6
    Last Post: 01-18-2012, 03:11 AM
  3. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM
  4. Filter to a date range ala Allen Brown
    By kekawaka in forum Forms
    Replies: 2
    Last Post: 10-10-2011, 12:19 PM
  5. Date Range filter in a Duplicate query
    By knickolyed in forum Forms
    Replies: 0
    Last Post: 06-27-2011, 04:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums