Filter data by using a form and open a report based on this data

Discussion in 'Microsoft Access Form Coding' started by Guest, Sep 26, 2007.

  1. Guest

    Guest Guest

    Hello,

    I created a form with 3 criterias: location, date to and from and course
    name. I have a reset button and a filter/search button. I want to open a
    report called "Sign-in" based on any of these criterias.

    Onclick I have filter all 3 criterias but I don't know how to open a report.
    I used the codes from previous posting:
    Private Sub cmdFilter_Click()
    ....
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtFilterLocation) Then
    strWhere = strWhere & "([Location] = """ & Me.txtFilterLocation &
    """) AND "
    End If

    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboFilterTrainingName) Then
    strWhere = strWhere & "([TrainingName] = " &
    Me.cboFilterTrainingName & ") AND "
    End If

    'Date field example. Use the format string to add the # delimiters and
    get the right international format.
    If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & "([TrainingDate] >= " &
    Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this
    field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
    strWhere = strWhere & "([TrainingDate] < " & Format(Me.txtEndDate +
    1, conJetDate) & ") AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    'For debugging, remove the leading quote on the next line. Prints to
    Immediate Window (Ctrl+G).
    Debug.Print strWhere
    Me.Filter = strWhere
    Me.FilterOn = True
    DoCmd.OpenReport "Sign-in Sheet", acViewPreview, , Me.Filter
    End If

    End Sub
     
    Guest, Sep 26, 2007
    #1
    1. Advertisements

  2. AccessProject wrote:
    >I created a form with 3 criterias: location, date to and from and course
    >name. I have a reset button and a filter/search button. I want to open a
    >report called "Sign-in" based on any of these criterias.
    >
    >Onclick I have filter all 3 criterias but I don't know how to open a report.
    >I used the codes from previous posting:
    >Private Sub cmdFilter_Click()
    >...
    >'Text field example. Use quotes around the value in the string.
    > If Not IsNull(Me.txtFilterLocation) Then
    > strWhere = strWhere & "([Location] = """ & Me.txtFilterLocation &
    >""") AND "
    > End If
    >
    > 'Number field example. Do not add the extra quotes.
    > If Not IsNull(Me.cboFilterTrainingName) Then
    > strWhere = strWhere & "([TrainingName] = " &
    >Me.cboFilterTrainingName & ") AND "
    > End If
    >
    > 'Date field example. Use the format string to add the # delimiters and
    >get the right international format.
    > If Not IsNull(Me.txtStartDate) Then
    > strWhere = strWhere & "([TrainingDate] >= " &
    >Format(Me.txtStartDate, conJetDate) & ") AND "
    > End If
    >
    > 'Another date field example. Use "less than the next day" since this
    >field has times as well as dates.
    > If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
    > strWhere = strWhere & "([TrainingDate] < " & Format(Me.txtEndDate +
    >1, conJetDate) & ") AND "
    > End If
    >lngLen = Len(strWhere) - 5
    > If lngLen <= 0 Then
    > MsgBox "No criteria", vbInformation, "Nothing to do."
    > Else
    > strWhere = Left$(strWhere, lngLen)
    > 'For debugging, remove the leading quote on the next line. Prints to
    >Immediate Window (Ctrl+G).
    > Debug.Print strWhere
    > Me.Filter = strWhere
    > Me.FilterOn = True
    > DoCmd.OpenReport "Sign-in Sheet", acViewPreview, , Me.Filter
    > End If
    >
    >End Sub



    I don't understand why you are messing with the form's
    Filter, but from where I sit the rest of the code looks like
    it should work. Did you try it? If not, why not? If you
    did, what about it didn't do what you want?

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Sep 27, 2007
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Filter a report based on a single field in open form

    Guest, Jul 25, 2006, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    256
    Guest
    Jul 26, 2006
  2. bymarce

    Filter Report based on forms filter

    bymarce, Jul 11, 2008, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    280
    bymarce
    Jul 15, 2008
  3. Rich_in_NZ

    Open form and filter form & subform using combo-boxes

    Rich_in_NZ, Dec 26, 2008, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    365
    John Smith
    Dec 30, 2008
  4. Replies:
    3
    Views:
    1,034
    Ron2006
    Jun 1, 2009
  5. DawnTreader

    filtering a form based on a filter on an open form

    DawnTreader, Oct 25, 2011, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    457
    DawnTreader
    Oct 25, 2011
Loading...

Share This Page