Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often need to add their own specialized tools.
This article will show how an unbound form (not linked to a table) can be created to let the user enter criteria for one or several fields, for example before opening anther form or a report. The central idea is to use the controls on the form directly as criteria for a query, which is then used as record source for a data form or a report.
Novice developers need to learn this method quite early, and it can luckily be applied with almost no Visual Basic coding. More advanced techniques are described in the next two other articles: Building a Custom Filter, and Custom Filters using BuildCriteria. Although they are meant to be read in sequence, more experienced developers will probably not do so.
____________________
The demo database is the same for all three articles. It contains tables from the well know Northwind Traders database, from a rather old version, as sample data. The forms are all variations on the common theme custom filtering.
FormFilters.mdb
Built-in Filtering
When a form bound to a table is opened in the interface, several shortcuts become available to create and manage both the filtering and the sorting of the data. They manipulate the form's current filter property.
The menu and toolbar or the ribbon offer things like filtering by selection, filter by form, and advanced filter.
From the context-menu, it is often possible to filter by selection directly.
The filter can be toggled on and off (in recent versions directly from the navigation bar).
The filter can be cleared in order to start afresh.
Advanced filtering even allows saving a filter and loading a saved filter, in an interface much like the query grid.
The present article isn't about the built-in features, but they had to be mentioned. Useful as they are, many developers want to create their own filtering form, and many users feel more comfortable with tools designed more specifically for their needs.
It must be understood, however, that a custom tool will not have the versatility of the combined built-in features. The first version of any custom filtering form will almost certainly lack one or two essential features. For example, if the form offers a combo box to select the country for a mailing, the first user testing it might say: “How can I select both Switzerland and Liechtenstein? I always merge these mailings.” This example is tricky both for the interface (add a second combo box labelled “or country”? create a special code for both countries? add Liechtenstein automatically?) and for the query (the expressions will become complicated to handle multiple combos or special cases). The simple criteria forms presented here have their limits, and it might not be possible to find a solution for certain demands.
Overview
A blank unbound form is used, and all data related elements are removed (record selectors and navigation). The scroll bars are removed and the border is set to dialogue mode. This makes it obvious to the users that it isn't a data entry form.
The form in the figure has only two combo boxes and a check box. These types of controls are useful for several reasons. For the developer, it's very convenient that the user can't just type anything, which could cause all sorts of errors or misunderstandings (“I typed `all categories, please´ and nothing came up!”). For the user, it is naturally much more convenient to select a category number by selecting its name, and the category number might in fact never be visible in the interface anyway.
It is also quite safe to use text boxes formatted as number or as date. The fields will only accept valid entries, and you can rely on this fact. The [OK] button will typically open a form or a report using the controls on the form.
Creating a Query using the Criteria Form
The first step is to create a query, and to learn how to read the current data in specific controls on the criteria form. This query can then be used as record source for a data form or a report.
For example, let's call the form above frmReportFilter and the first combo box cboCategory. The current value of that particular control can be used anywhere in Access as
Forms!frmReportFilter!cboCategory
In the case of a combo box, the value returned by the expression is the bound column, for example “1” when “Beverages” is selected. In other words, it is related to fields like CategoryID, and not CagegoryName. This makes sense when filtering the list of products, since each product is assigned to a category by number, and not by name.
The query will show only those products where the field CategoryID matches the current value of the combo box on the form. Some square brackets have been added; they are really needed only if the form or control names contain spaces or other invalid characters. They do no harm, as long as opening and closing brackets match.
Note: The figure shows that the form is open in form view. This is necessary because, if the form is closed, the query would ask for parameters, namely the cryptic expression to obtain a category ID number (exactly as is appears in the figure).
Similar expressions can be used for the other two controls on the form. The supplier is also identified by an ID, and we don't even need to know whether it's a number or an alphanumeric key; the syntax is the same in both cases.
Handling Missing Criteria Values
This is a subtle problem. What the user wishes to see all products of a supplier, and leaves the category blank? The expression returns Null, and the criteria as written will prevent any product from being selected, even products having a Null category! A comparison with Null becomes itself Null, and is interpreted as False in this context.
The same is true for the checkbox “discontinued”. The user cannot get a list of all products, disregarding their status. The control itself can be made tri-state, cycling through True, False, and Null. But again, selecting Null will yield no products.
Over the years, thousands of novice developers have developed solutions to show all products when no category is selected, some of them extraordinarily creative and ingenious. They often involve the criteria syntax “Like '*'”, and can prompt the question about Adding the option “all” to a ComboBox. A simpler solution using an expression (in SQL view for a change):
WHERE CategoryID Like Nz(Forms!frmReportFilter!cboCategory, '*')
The function Nz() (for null-to-zero) substitutes a Null value with the second argument. When the combo returns a value, this expression becomes, say, “Like '7'”; when the combo is Null, it will be “Like '*'”. This works in many cases. However, it will not return any products without a category (the field needs to contain something to be “like '*'”) and it will treat the category ID as text, not as number. The first problem is irrelevant if the category is required. The second is more technical.
When using the Like operator, the field CategoryID is automatically converted to string, which is then matched against the pattern on the right side of the operator. Converting numbers to string is very fast, and you will not see any noticeable decrease in performance on a table with a couple of hundred products. Technically, however, this is an unnecessary operation and it further prevents query optimization (this is beyond the scope of the article, but some guidelines are suggested below). When working with larger tables, performance becomes an important issue. In a given situation, the query using “CategoryID=7” might run in under a second while “CategoryID Like '7'” takes minutes...
This being said, since the category ID is being converted to text, why not solve the first problem like this?
WHERE Nz(CategoryID) Like Nz(Forms!frmReportFilter!cboCategory, '*')
This criteria will return all products, even those where the category ID is Null. Converting both the field and the criteria expression to string removes the problem altogether, but again, it forces the database engine to actually read and process all records (a so-called full-table scan).
In the long run, it is best to pay close attention to data types, and to help the database engine in optimizing your queries by following a simple rule. Use only predicates in the form:
‹field name› ‹operator› ‹expression›
... and to combine individual predicates with “And”, “Or”, and bracketing, nothing else. The data type of the expression should match that of the field in order to avoid implicit conversions. However, it is sometimes possible to switch the field and the operator, a variation we will put to good use.
Under the “best practice” constraints above, one way to deactivate the criteria completely when no entry was made in a control is to use “Or” to combine it with another, testing specifically for that case. In plain English: “either the combo is empty, or it matches the field”. The expression below uses only the short name of the combo box, something that is possible under certain circumstances explained below.
WHERE cboCategory Is Null Or CategoryID=cboCategory
The query optimizer can (and likely will) remove the entire criteria before even opening the table: a condition that is always true is normally eliminated from the start.
In the Access query grid, this syntax would require a second row. Adding a second similar criteria isn't simple at all, and will end up using four rows. It is now time to use the alternative writing with the field and the operator switched:
WHERE (cboCategory Is Null Or cboCategory=CategoryID)
This is the same to the database engine, but it looks much nicer in the query grid, and allows adding more similar criteria easily. Notice also the parentheses, which are essential as soon as you start adding more criteria in SQL view; in normal design view, they will be added for you.
If you take only one thing from this article, let it be this trick, you will use it often.
Based on the same template, the other fields can be added. The query now happily shows all records before a selection is made, and behaves in a predictable manner with partial selections.
Before looking into the next tricky problem, handling text input, I want to explain when the abbreviated name of the control can be used.
Embedded Queries on a Form
We have assumed up to now that the query will be run independently, in another window, as a stand-alone query or as record source for another interface object. Some queries, however, are run from within the same form as the controls used in the criteria. This is the case especially for the row source of combo boxes and list boxes. If a list box is itself on the form frmFilter, and it uses the combo cboCategory on the same form, the short name can be used instead of the full name
Forms!frmFilter!cboCategory is cboCategory
Building up from the form in the previous section, let's add a list box on the same form (see figure 4), creating a preview of the products selected by the current criteria. The row source of the list box is, in SQL view and stripped to bare essentials:
SELECT ProductID, ProductName
FROM Products
WHERE (cboCategory Is Null Or cboCategory=CategoryID)
AND (cboSupplier Is Null Or cboSupplier=SupplierID)
AND (chkDiscontinued Is Null Or chkDiscontinued=Discontinued)
ORDER BY ProductName
You will recognize the category criteria explained above; supplier and discontinued criteria are similar. Formally, and this is true for both the short and the full names of the controls, the names of the three controls become parameters. To make this explicit, it is possible and sometimes desirable to declare them, like this:
I will not go into explicit parameter declaration here, I merely wanted to stress once more that each field has a data type and that the criteria should be compatible. If the query without declaration is run alone, you still get prompts for the parameters, but the engine will accept any entry you make. With typed parameters, it will only accept valid entries (numbers in this case). To visualize the problem, imagine your teacher asking you whether a statement is True or False. For “ 2 = 2 ”, the answer is simple; for “ 'A' = 'a' ”, it depends on an additional rule (this statement is true in Access queries). But what about “ 2 = '2' ”? A number and a string of one digit are not the same type of object, so there is no good answer. When such a criteria is accepted, if will force a conversion, in one direction or the other.
Note: The short name only works if it can't be confused with a field name. If we had named the combo box “CategoryID”, then the full name would be needed to remove the ambiguity. Creating a parameter with the same name as a field effectively masks the field from the query and the parameter is used instead everywhere.
The note above is one more reason to use a sound naming convention. Notice that all controls in this article have a prefix, making it much less likely to create ambiguous names, and making them much more readable. Using “Combo12”, “Text128”, or “Category” as name is of course possible, but I like to know what type of control I'm dealing with and also what field it might relate to. This is even more important when referring to the controls from other database objects.
The finished Criteria Form in the demo
The four products displayed are in the same category, and are discontinued. The list updates dynamically as selections are made. The query shows the same list with a few more fields; it can be replaced by a form or a report using that query as record source.
The Visual Basic Module behind the form is relatively simple, and the only essential feature, opening another object, can be created through an assistant. The button [clear filters] simply writes Null into the three controls, [row source?] displays a message box showing the query from the last section, [open query] opens a stand-alone query using the full names of the controls on the form as criteria.
Finally, each criteria control's `after update´ event calls a simple function forcing the preview list box to requery. This is what makes this form dynamic (and more pleasant to test). Please take the time to take it apart and examine all controls in design view.
Adding text searches
Allowing for text searches and implementing them poses a few additional problems, and I thought it best to first create a form without them. Based on the form above, let's now add two text boxes, and use them to search the product name and a second field, called QuantityPerUnit, containing packaging information such as “500 g” or “12 – 8 oz jars”.
If we were filtering for simpler text, say the country of origin, it would be possible to use exactly the same technique as before, searching for a full string match. As it stands, the user probably needs to search inside of the text, for example “crab” or “sauce” in the name, or “jars” in the packaging. This brings us back to the operator `Like´. To find all tofu brands, this criteria will work:
WHERE ProductName Like '*tofu*'
You can use double-quotes or single quotes to enclose literals. Replacing the literal with a reference to the text box on the form can be done like this:
WHERE ProductName Like '*' & txtProductName & '*'
Note that when there is no text, the criteria becomes “Like '**'”, which will effectively display all non-Null product names. Since the name is certainly required, it already works as expected, with only a slight performance loss. If the query optimizer uses the fact that the field is required, it could possibly eliminate the criteria, seeing that it will be true for all records (I don't believe this is the case).
If the field can be Null, the same expression as above can be used.
WHERE (txtPackaging Is Null Or QuantityPerUnit Like '*'+txtPackaging+'*')
I used “+” instead of “&”, which has a slightly different meaning, but it has no impact in this case. The former expression would result in “Like '**'” if the product name is empty, which is often True, while the latter becomes “Like Null” when the packaging is empty, which is always False. Since both expressions are masked by the direct test for Null, how they behave on Null doesn't matter. I showed the “+” operator so that you are not surprised to see in the demo database, as I tend to use it often.
So, the point is that we effectively disable the criteria when the text box is null, allowing all products to be returned even if the field is Null. However, we can no longer switch the expression with the field, because the operator is asymmetrical (it isn't commutative) and because the text box reference is embedded in an expression.
If more than one field require this criteria, it is no longer possible to manage the query in the query design grid. It isn't really that hard to design in SQL view, possibly using copy-paste from other queries created in the query grid, and it's a good occasion to start doing so.
Make it a habit to save your SQL queries elsewhere, however. Switching to design view is quite natural and it's just too easy to click on save when the design grid has totally mangled your query. Please try it in the demo database. The query qpselProductsText (the prefix “qpsel” means “parametric select query” in the naming convention I use, by the way) looks quite nice in SQL view, works fine in datasheet view, and becomes horribly complicated once you switch to the normal design view. I keep a backup in case I click save at the wrong time: zbqpselProductsText (now a “backup of a parametric select query”... reaching the limit of my tolerance for cryptic object prefixes...).
The second Criteria Form in the demo
The products displayed are condiments packaged in jars. The Visual Basic code is very similar to that of the previous form, there is one additional button to open the query in design view (it will open in SQL view because it was last saved that way). Again, no code is strictly required besides the opening of some meaningful object using the criteria form, and that code can be created by the control button wizard.
As a final note, the queries we are creating in this article will become the record source of other objects, filtering the records at the source, so to speak. A form based on such a query will not appear to be filtered, and there isn't any way for the user to remove the embedded filter. The form will appear unfiltered, unless additional filtering is added on top of it, naturally.
However, the form can be requeried, for example using Shift+F9 or a tool like “refresh all”. At that moment, the current values of the form are used to regenerate the list of records. Since the criteria form usually has to stay open, it is absolutely possible to let the user change the filter there and then to issue a requery of the dependant form. If the criteria form is closed and a requery is triggered, the user will be prompted to re-enter values for each parameter.
Criteria Forms and Reports
When such a form is used to print or preview reports, it often makes sense to somehow document the current filter used on the report. This is relatively easy: you can create text boxes with expressions referring to the (still open) criteria form, just like in the query itself. For example:
='Category: '+Forms!frmFilter!cboCategory.Column(1)
='Product name contains: “'+Forms!frmFilter!txtProductName+'”'
=Format(Forms!frmFilter!chkDiscontinued, ';"active";"discontinued";"(both)"')
For combo boxes, the displayed name is available as “column 1”, “column 0” being the hidden bound column with the category number. The next two examples show more concatenation techniques that can be used to make the raw content more readable for printing. A thorough explanation can be found in the article Concatenation in Reports (and Forms).
Conclusion
Creating the first criteria form is very rewarding. The interface starts to look like an actual application, with objects interacting with one another, and the result is often truly useful.
This simple method has its limits, of course. For example, it cannot handle multiple selections in a list box, or manipulate the filter of an open form. As such, it is mainly used for printing reports. For more complex cases, the filter needs to be built through Visual Basic programming, and this is the topic of the next article in this mini-series.
I have tried to show the pitfalls of criteria forms design and how to avoid them. I hope you will now find them easier to create and maintain.
Again, I would like to thank SowleMan for proof-reading my first draft.
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-bookunrestricted Access.
Thanks Markus, you have saved me numerous times before. This is no exception. Precise and detailed explanation, makes it so easy to understand. Five Stars!!
whao harfang, really really good article and tips! = )
by the way guys, this short quote from the follow webpage helped me understand the + and & difference better! hope it helps!
In VBA and Jet/ACE SQL the & concatenation operator ignores Null (Null & "" = "") while the + concatenation operator propagates Nulls (Null + "" = Null). Null concatenation is quite useful in expressions like Mid(("12 + LastName) & (", " + FirstName), 3), but you have to be careful not to try to use it with numeric fields (or strings that can be implicitly coerced to numeric values), since while "12" + Null will propagate the Null, "12" + "8" MAY add the numeric values of the two strings (it depends on the context). – David-W-Fenton Sep 10 '10 at 19:30
I learn better when using the actual example databases, but when I downloaded this one (FormFilters.mdb) from above, I kept getting the error, "access 2013 the expression after update you entered as the event property setting produced the following error: The expression you entered has a function name that Microsoft Access can't find." when trying to use the forms. Can you tell me why or if there's something I need to do before they will work?
Have a question about something in this article?
You can receive help directly from the article author.
Sign up for a free trial to get started.
Comments (4)
Commented:
Thank you
Commented:
Commented:
by the way guys, this short quote from the follow webpage helped me understand the + and & difference better! hope it helps!
http://stackoverflow.com/questions/3627526/vba-problem-with-string-concatenation
Commented: