How to send filtered results to Reports??

[Viken Karaguesian]

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.

 

[Al Camp]

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.

 

[Viken Karaguesian]

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

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

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.

 

[Al Camp]

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

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

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

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.