Query and Filter Problems (Access 2000)

Topic

New Reply

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???

Reply | Quote

Replies

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]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Here’s a stripped down version of my database.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Works great now! Thanks so much, Hans.

Reply | Quote