Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query and Filter Problems (Access 2000)

    I'm having a bit of trouble with my queries and filters. I have a Customers table that lists all the customers and how much they owe. I also have a Schools table that lists all the schools that the customers could be from.

    I have a form that has a drop down combo box that looksup the Schools table. I want to be able to select a school from the combo box, then do a query and filter the records by the school selected. I have a query called Status Report. This queries all the customers (LastName, FirstName, SchoolName, Owe). How do I filter that query to only show me the customers that are for the school I selected, and how do I print out a report that uses that filter, too???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query and Filter Problems (Access 2000)

    Say that your form is named frmSelect and that the combo box is named cboSchool.
    Open the Status Report query in design view.
    Enter the following in the Criteria line under SchoolName:

    [Forms]![frmSelect]![cboSchool]

    When you open the query, or a form or report that uses the query as record source, only customers for the selected school will be displayed.
    You can display the name of the selected school on the report, for example in the report header or page header section, by creating a text box with Control Source

    =[Forms]![frmSelect]![cboSchool]

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query and Filter Problems (Access 2000)

    I gave that a try, but it doesn't seem to be working. When I put in [forms]![Families]![cmbSchoolName] in the Status Report query under the School field, I don't get any records. Could it be because my combo box is a lookup, and the actual school names are in the School field of the Status Report query? Does it take the autonumber from the Schools table (that my combo box refers to) and try to match it to my School field in my query? The School field has the actual names of the schools in it, not a number.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query and Filter Problems (Access 2000)

    That may well be the problem. Please check the design of the various fields in the tables and of the combo boxes, and provide detailed information, or post a stripped down copy of the database.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query and Filter Problems (Access 2000)

    Here's a stripped down version of my database.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query and Filter Problems (Access 2000)

    Your Customers table should not contain a School (text) field, but a SchoolID (numner) field, linked to the SchoolID field in Schools. Once you have added a SchoolID field to Customers, you can use an update query based on Customers and Schools linked on School vs SchoolName to populate it. After running the update query, you can remove the School field from Customers. Create a relationship between the tables on SchoolID, with referential integrity enforced.

    The School text box on the form must be changed to a SchoolID combo box.

    The query should contain the SchoolID field, and the criteria should be for this field.

    See attached modified version.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query and Filter Problems (Access 2000)

    Now I understand it. Thanks, Hans. The only thing, though, that I can't seem to get working now is the title on my report. I want it to print the school name that I had selected. I did a text box with =[forms]![Families]![cmbSchoolName]. It shows the number of the school but I want the school name.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query and Filter Problems (Access 2000)

    Instead of a text box, use a combo box, with:

    Column Count: 2
    Column Widths: 0";1"
    Control Source: =[Forms]![Families]![cmbSchoolName]
    Row Source Type: Table/Query
    Row Source: qrySchools (or Schools)

    When you preview or print the report, the combo box will look like a text box, but it will look up the school name corresponding to the School ID.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query and Filter Problems (Access 2000)

    Works great now! Thanks so much, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •