 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2005Adding a filter to a report
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) > 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 [quoted text clipped - 11 lines] > boys at once with a filter but that would be our first question. > David 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: [quoted text clipped - 31 lines] >>boys at once with a filter but that would be our first question. >>David 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.  SignatureHTH 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 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 > 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. 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. SignatureHTH Van T. Dinh MVP (Access) > 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 [quoted text clipped - 10 lines] > use it enough. > David 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 > 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. 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. SignatureHTH Van T. Dinh MVP (Access) > 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" [quoted text clipped - 4 lines] > The help section didn't make it clear enough for me. > David Perfect!! I would have never figured that out. Thank you very much. David > Use the expression I posted in an empty Column of the Query Design grid. > [quoted text clipped - 4 lines] > 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 > Use the expression I posted in an empty Column of the Query Design grid. > [quoted text clipped - 4 lines] > other values. In your Reports / Labels, you can use the Calculated Fields > just like other Fields. 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. SignatureHTH Van T. Dinh MVP (Access) > I'm curious why the expression uses "IIf" instead of just "If". Any > particular reason for IIF? > David |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.