Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to send filtered results to Reports??

Discussion in 'Information Technology' started by Viken Karaguesian, Jul 28, 2006.

  1. Viken Karaguesian

    Viken Karaguesian
    Expand Collapse
    Guest

    Hello everyone,

    I have another question: I want to be able to send filtered results to a
    mailing label report or an invoice report.

    Here's the situation: I have a mailing list. The main form contains all the
    information on each person, including different types of codes that identify
    them (if they're donors, members, supporters, etc). Say I filter this
    information and end up with a filtered list of 140 out 1000 people. How can
    I send this filtered information to a ready made Report? Imagine you
    filtered several times: All "members" from a specific organization that live
    in a specific city. You want to mail only these selected people a letter,
    for instance.

    I have figured out a way to do this, but for only *one* field. I created a
    new form with an unbound combo box that contains a value list of these codes
    (donor, member, supporter, etc). Then, in a query, I included this
    expression: [Forms]![Name of Form]![Name of Combo Box]. I then created a
    Mailing Label report based on that query. So...I open a form, choose a
    "class" from the Combo box and the mailing labels are printed with only that
    selection. (I'm obviously glossing over a couple of details, but I hope you
    all get the idea).

    However, that only works for ONE selection and ONE field. What if, in my
    Main Form, I had a specific set of names filtered (using several filters)
    and want to output those names to a Label report or an Invoice report, as
    described above? I'm not sure how I would do that.

    --
    Viken K.
    http://www.vikenk.com
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Why This Sikh Cartoonist Is Sending A Postcard To Trump Every Day Breaking News Mar 23, 2016
    UK Statue of turbaned Sikh fighter pilot unveiled in Gravesend Breaking News Nov 29, 2014
    Leisure Appropriate to send flowers to a friend's ill Mother in India? Business, Lifestyle & Leisure Jun 6, 2013
    USA Preet Bharara Sends a Message to Wall Street about Insider Trading Breaking News May 18, 2011
    USA U.S. Sends Warning to People Named in Cable Leaks Breaking News Jan 7, 2011

  3. Al Camp

    Al Camp
    Expand Collapse
    Guest

    Viken,
    To add a filter for city, just add another field to your dialog form ([City]).
    As a criteria in your City field of the report query...
    Like "*" & [Forms]![Name of Form]![City] & "*"
    If you eneter a city name, it will filter for that value.
    If you leave it Null, it will return all cities.
    Use the same process for the other fields you may want to filter for.
    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions

    "Viken Karaguesian" <vikenkNO_SPAM@NO_SPAMcomcast.net> wrote in message
    news:t7GdnTYPpM9hJ-rZnZ2dnUVZ_vydnZ2d@comcast.com...
    > Hello everyone,
    >
    > I have another question: I want to be able to send filtered results to a mailing label
    > report or an invoice report.
    >
    > Here's the situation: I have a mailing list. The main form contains all the information
    > on each person, including different types of codes that identify them (if they're
    > donors, members, supporters, etc). Say I filter this information and end up with a
    > filtered list of 140 out 1000 people. How can I send this filtered information to a
    > ready made Report? Imagine you filtered several times: All "members" from a specific
    > organization that live in a specific city. You want to mail only these selected people a
    > letter, for instance.
    >
    > I have figured out a way to do this, but for only *one* field. I created a new form with
    > an unbound combo box that contains a value list of these codes (donor, member,
    > supporter, etc). Then, in a query, I included this expression: [Forms]![Name of
    > Form]![Name of Combo Box]. I then created a Mailing Label report based on that query.
    > So...I open a form, choose a "class" from the Combo box and the mailing labels are
    > printed with only that selection. (I'm obviously glossing over a couple of details, but
    > I hope you all get the idea).
    >
    > However, that only works for ONE selection and ONE field. What if, in my Main Form, I
    > had a specific set of names filtered (using several filters) and want to output those
    > names to a Label report or an Invoice report, as described above? I'm not sure how I
    > would do that.
    >
    > --
    > Viken K.
    > http://www.vikenk.com
    >
    >
    >
     
  4. Viken Karaguesian

    Viken Karaguesian
    Expand Collapse
    Guest

    > To add a filter for city, just add another field to your dialog form
    > ([City]).
    > As a criteria in your City field of the report query...
    > Like "*" & [Forms]![Name of Form]![City] & "*"


    That's a good suggestion. I could just make one giant filter form with that
    expression in each field. However, I'd first like to figure out how to do it
    from the main form.

    If the user is in the Main Database Form and right-clicks on a field and
    chooses "Filter by Selection" or "Filter Excluding Selection", I'd like to
    be able to take those filtered results and send them to a Report (either
    mailing labels or an invoice). That way I wouldn't have to have an extra
    form for filtering purposes. Am I making sense?

    Also, could you please elaborate on the expression you used? "*" &
    [Forms]![Name of Form]![City] & "*"

    I could just plug it in and make it work, but I'd also like to understand
    what it means. It seems like the ampersand is the connector between the
    fields, which ties them together. Why is there an asterisk before and after
    the expression? What do the exclamation points mean?

    As an aside, I see you live in NH. How did you fare in all the rain from
    last week? I live in the Boston area. I'll be up in Laconia tonight, on Lake
    Winnisquam. It'll be interesting to see how high the lake's water level is
    :>)

    --
    Viken K.
    http://www.vikenk.com


    "Al Camp" <anon@anon.net> wrote in message
    news:%23OmEW4TgGHA.1272@TK2MSFTNGP03.phx.gbl...
    > Viken,
    > To add a filter for city, just add another field to your dialog form
    > ([City]).
    > As a criteria in your City field of the report query...
    > Like "*" & [Forms]![Name of Form]![City] & "*"
    > If you eneter a city name, it will filter for that value.
    > If you leave it Null, it will return all cities.
    > Use the same process for the other fields you may want to filter for.
    > --
    > hth
    > Al Camp
    > Candia Computer Consulting - Candia NH
    > http://home.comcast.net/~cccsolutions
    >
    > "Viken Karaguesian" <vikenkNO_SPAM@NO_SPAMcomcast.net> wrote in message
    > news:t7GdnTYPpM9hJ-rZnZ2dnUVZ_vydnZ2d@comcast.com...
    >> Hello everyone,
    >>
    >> I have another question: I want to be able to send filtered results to a
    >> mailing label report or an invoice report.
    >>
    >> Here's the situation: I have a mailing list. The main form contains all
    >> the information on each person, including different types of codes that
    >> identify them (if they're donors, members, supporters, etc). Say I filter
    >> this information and end up with a filtered list of 140 out 1000 people.
    >> How can I send this filtered information to a ready made Report? Imagine
    >> you filtered several times: All "members" from a specific organization
    >> that live in a specific city. You want to mail only these selected people
    >> a letter, for instance.
    >>
    >> I have figured out a way to do this, but for only *one* field. I created
    >> a new form with an unbound combo box that contains a value list of these
    >> codes (donor, member, supporter, etc). Then, in a query, I included this
    >> expression: [Forms]![Name of Form]![Name of Combo Box]. I then created a
    >> Mailing Label report based on that query. So...I open a form, choose a
    >> "class" from the Combo box and the mailing labels are printed with only
    >> that selection. (I'm obviously glossing over a couple of details, but I
    >> hope you all get the idea).
    >>
    >> However, that only works for ONE selection and ONE field. What if, in my
    >> Main Form, I had a specific set of names filtered (using several filters)
    >> and want to output those names to a Label report or an Invoice report, as
    >> described above? I'm not sure how I would do that.
    >>
    >> --
    >> Viken K.
    >> http://www.vikenk.com
    >>
    >>
    >>

    >
    >
     
  5. Al Camp

    Al Camp
    Expand Collapse
    Guest

    Viken,
    First, the expression was... ("Like" is part of the expression)
    Like "*" & [Forms]![Name of Form]![City] & "*"
    actually, since your using the full city name...
    Like [Forms]![Name of Form]![City] & "*"
    would do the same.
    The "*" is a global variable, which by itself would say "Return all records". But the
    [Forms]![Name of Form]![City] "qualifies" that global to show only the city you chose to
    filter on... if there is one. If not, the global takes over to show all.

    As far as the !s, (called a "bang" in access) it is the normal object separator when
    addressing objects in Access VB.
    ex.
    Forms!frmMainForm!SomeField 'reference [SomeField] on the main form
    Forms!frmMainForm!frmSubForm.Form!SomeField 'reference [SomeField] on the
    subform
    Check Help on "referencing"...objects and properties

    Regarding using the filter presently applied to a form to filter the report
    accordingly, I used thia code in the report OnOpen event.
    It says "If the form is filtered right now, use the same filter against the report
    data... If not, use the current CustID on the form as the filter.
    Private Sub Report_Open(Cancel As Integer)
    If Forms!frmCustomers.FilterOn = False Then '** Is it filtered?
    Me.Filter = "CustID = Forms!frmCustomers!CustID"
    Me.FilterOn = True
    Else ' If not then...
    Me.Filter = Forms!frmCustomers.Filter
    Me.FilterOn = True
    End If
    End Sub

    Say you form filtered for 50 records out of 500 for "Boston". If so, the report
    delivers all 50 records.
    If Not, it delivers just the unique CustID for the reord displayed on the form.

    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions




    "Viken Karaguesian" <vikenkNO_SPAM@NO_SPAMcomcast.net> wrote in message
    news:nqKdnZar44t18-XZRVn-iA@comcast.com...
    >> To add a filter for city, just add another field to your dialog form ([City]).
    >> As a criteria in your City field of the report query...
    >> Like "*" & [Forms]![Name of Form]![City] & "*"

    >
    > That's a good suggestion. I could just make one giant filter form with that expression
    > in each field. However, I'd first like to figure out how to do it from the main form.
    >
    > If the user is in the Main Database Form and right-clicks on a field and chooses "Filter
    > by Selection" or "Filter Excluding Selection", I'd like to be able to take those
    > filtered results and send them to a Report (either mailing labels or an invoice). That
    > way I wouldn't have to have an extra form for filtering purposes. Am I making sense?
    >
    > Also, could you please elaborate on the expression you used? "*" & [Forms]![Name of
    > Form]![City] & "*"
    >
    > I could just plug it in and make it work, but I'd also like to understand what it means.
    > It seems like the ampersand is the connector between the fields, which ties them
    > together. Why is there an asterisk before and after the expression? What do the
    > exclamation points mean?
    >
    > As an aside, I see you live in NH. How did you fare in all the rain from last week? I
    > live in the Boston area. I'll be up in Laconia tonight, on Lake Winnisquam. It'll be
    > interesting to see how high the lake's water level is :>)
    >
    > --
    > Viken K.
    > http://www.vikenk.com
    >
    >
    > "Al Camp" <anon@anon.net> wrote in message
    > news:%23OmEW4TgGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> Viken,
    >> To add a filter for city, just add another field to your dialog form ([City]).
    >> As a criteria in your City field of the report query...
    >> Like "*" & [Forms]![Name of Form]![City] & "*"
    >> If you eneter a city name, it will filter for that value.
    >> If you leave it Null, it will return all cities.
    >> Use the same process for the other fields you may want to filter for.
    >> --
    >> hth
    >> Al Camp
    >> Candia Computer Consulting - Candia NH
    >> http://home.comcast.net/~cccsolutions
    >>
    >> "Viken Karaguesian" <vikenkNO_SPAM@NO_SPAMcomcast.net> wrote in message
    >> news:t7GdnTYPpM9hJ-rZnZ2dnUVZ_vydnZ2d@comcast.com...
    >>> Hello everyone,
    >>>
    >>> I have another question: I want to be able to send filtered results to a mailing label
    >>> report or an invoice report.
    >>>
    >>> Here's the situation: I have a mailing list. The main form contains all the
    >>> information on each person, including different types of codes that identify them (if
    >>> they're donors, members, supporters, etc). Say I filter this information and end up
    >>> with a filtered list of 140 out 1000 people. How can I send this filtered information
    >>> to a ready made Report? Imagine you filtered several times: All "members" from a
    >>> specific organization that live in a specific city. You want to mail only these
    >>> selected people a letter, for instance.
    >>>
    >>> I have figured out a way to do this, but for only *one* field. I created a new form
    >>> with an unbound combo box that contains a value list of these codes (donor, member,
    >>> supporter, etc). Then, in a query, I included this expression: [Forms]![Name of
    >>> Form]![Name of Combo Box]. I then created a Mailing Label report based on that query.
    >>> So...I open a form, choose a "class" from the Combo box and the mailing labels are
    >>> printed with only that selection. (I'm obviously glossing over a couple of details,
    >>> but I hope you all get the idea).
    >>>
    >>> However, that only works for ONE selection and ONE field. What if, in my Main Form, I
    >>> had a specific set of names filtered (using several filters) and want to output those
    >>> names to a Label report or an Invoice report, as described above? I'm not sure how I
    >>> would do that.
    >>>
    >>> --
    >>> Viken K.
    >>> http://www.vikenk.com
    >>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page