Adding a filter to a report

  • Thread starter Thread starter David G
  • Start date Start date
D

David G

I'm the registrar for a small summer camp program and I have all the
kids names, addresses, cabin assignments etc in an Access database. How
do I add a filter to print a report? I want to be able to print a
roster of just the boys and/or just the girls. I have a field for sex
and have an "M" or "F" in each record. I already have my report
designed and working except it shows both sexes in the datebase. What
do I modify to get a report of just the girls or just the boys?
Also I need to need out a bus schedule. I have a label designed and in
place but it is for the boys only as the first word of the first line
needs to be either "Mr." or "Ms." Is there a way to get access to print
a Mr. for the camps with an "M" in the garage OR a "Miss" for the girls
whom have an "F" in the SEX field. I can print them all at once OR
manually change the MS and Mr manually if I knew how to print all the
boys at once with a filter but that would be our first question.
David
 
1. You can use a parametrised Query as the RecordSource for Report. The
Parameter in the query should be something like:

WHERE (tblKid.[SEX] = [Enter Gender (M or F):])
OR ([Enter Gender (M or F):] Is Null)

If you enter "M", you get the boys. If you enter "F", you get the girls.
If you enter nothing, you get both boys & girls.

2. In the Query you use as the RecordSource for the Labels, you can use a
calculated Field like:

KidTitle: IIf( [SEX] = "M", "Mr", IIf( [SEX] = "F", "Miss", "" ) )

Then you can use this to print the Labels with appropriate KidTitle.

HTH
Van T. Dinh
MVP (Access)
 
Van,
You seem to understand exactly what I need to do. I don't understand
where and how to enter a "papametrised Queery" or a RecordSource. Could
you point me to a resource to explain it or tell me how to do that. I
have played with the menu bar and can't find either of those.
David
1. You can use a parametrised Query as the RecordSource for Report. The
Parameter in the query should be something like:

WHERE (tblKid.[SEX] = [Enter Gender (M or F):])
OR ([Enter Gender (M or F):] Is Null)

If you enter "M", you get the boys. If you enter "F", you get the girls.
If you enter nothing, you get both boys & girls.

2. In the Query you use as the RecordSource for the Labels, you can use a
calculated Field like:

KidTitle: IIf( [SEX] = "M", "Mr", IIf( [SEX] = "F", "Miss", "" ) )

Then you can use this to print the Labels with appropriate KidTitle.

HTH
Van T. Dinh
MVP (Access)



I'm the registrar for a small summer camp program and I have all the
kids names, addresses, cabin assignments etc in an Access database. How
do I add a filter to print a report? I want to be able to print a
roster of just the boys and/or just the girls. I have a field for sex
and have an "M" or "F" in each record. I already have my report
designed and working except it shows both sexes in the datebase. What
do I modify to get a report of just the girls or just the boys?
Also I need to need out a bus schedule. I have a label designed and in
place but it is for the boys only as the first word of the first line
needs to be either "Mr." or "Ms." Is there a way to get access to print
a Mr. for the camps with an "M" in the garage OR a "Miss" for the girls
whom have an "F" in the SEX field. I can print them all at once OR
manually change the MS and Mr manually if I knew how to print all the
boys at once with a filter but that would be our first question.
David
Click to expand...
Click to expand...
 
Check Access Help. I type "Parameter Query" and "RecordSource" in Help and
the right topics come up in the search.

Any Access book will cover these topics also.
 
OK, this may be harder than I thought especially since I don't undertand
all the terms in Access. Is RecordSource is really just the name of
the table my data is coming from?
And in the following statement you sent me:

WHERE (tblKid.[SEX] = [Enter Gender (M or F):])
OR ([Enter Gender (M or F):] Is Null)

is tblKid the name of a table and SEX the name of a field within that
table? I'll try to find a book on this but for a one time deal, it may
not be worth it. Sorry about my ignorance but Access has been the
hardest program I've ever tried to understand, partly because I don't
use it enough.
David
 
RowSource (of a Report) can be either a Table, a Query or an SQL String. I
rarely use Tables (some of my Tables have 1M+ Records) as RecordSources and
use Queries or SQL Strings to select only Records that meet certain
criteria. What I post is the criteria to select the required Records using
Query (which translates to an SQL String in the end) or an SQL String as the
RecordSource for the Report.
 
I got the gender select thing to work on the roster, very cool. I
couldn't figure out the label thing so I added another field for "Title"
and made it a combo list box to select either Mr. or Miss. Then I went
through all the kids and assigned a title. I just couldn't figure out
the calulated field of
KidTitle: IIf( [SEX] = "M", "Mr", IIf( [SEX] = "F", "Miss", "" ) )
The help section didn't make it clear enough for me.
David
 
Use the expression I posted in an empty Column of the Query Design grid.

If you open the Datasheet of the Query, this Column will show Mr or Miss
depending on the gender of the kid.

KidTitle is called a Caluculated Field since it is calculated / derived from
other values. In your Reports / Labels, you can use the Calculated Fields
just like other Fields.
 
I'm curious why the expression uses "IIf" instead of just "If". Any
particular reason for IIF?
David
 
If is a VBA construct / statement which can only be used in VBA.

IIf (Immediate-If) is an Access function (i.e. returning a value) which can
be used in Queries as well as VBA.

Most of these functions are covered in virtually every Access book.
 
Back
Top