Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Custom Filters using BuildCriteria()

Published:
Updated:
The first two articles in this short series — Using a Criteria Form to Filter Records and Building a Custom Filter — discuss in some detail how a form can be used as static criteria holder or to build a filter as a string, which is then passed or applied to other database objects.

This final article attempts to make these forms more versatile, by using the built-in function BuildCriteria, and discusses various problems encountered along the way. This is the most technical article in the series, and there will be little or no space for basic explanations. It is assumed that the reader is familiar with Visual Basic, at least passively.

____________________

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



The lack of Versatility


On a filter building form, let's imagine a control to enter a number. The user can type 10 or 42 to filter records with the desired value. In many cases, it is more meaningful to enter lower and upper bounds: <49, >=3, between 10 and 19... The solution is often to present an awkward and (to me) rather ugly cluster of controls. Open the conditional formatting dialogue and you will see what I mean. In that particular case, it is a good choice: it empowers the users as they can create conditions easily with very little technical background. However, another option in the same dialogue lets one type any expression.

This is what I am after: to provide a text box and let the user type some meaningful semi-coded string which is then turned into a syntactically correct predicate. The filter for a number should thus accept any of the following (in decreasing priority):
10
>=12
between 100 and 200
>10 and <=20
null
7 or 11
In (2,4,6,8)
The second example and similar predicates are easily programmed: detect if the first character or characters are a comparison operator, and use that instead of the default “=” when building the predicate. If need be, the same is true for the rest of the list, but it's increasingly difficult.

If used often, it's probably worthwhile to create a module for this task. It is however a real development project, which could grow into something unexpectedly large. Think about dates, complex text searches, logical expressions, switch expressions, ... not to mention all the friendly error messages to be written!

Access already does all that, and quite well. Can't we tap into that code base?



Introducting BuildCriteria()


From the help file: “The BuildCriteria method returns a parsed criteria string as it would appear in the query design grid, […]”. This isn't accurate, in that the function returns a criteria string for SQL, and not as it would appear to the user, but let's leave that for later. The function does parse an arbitrary string and makes a fair attempt at translating that string into a valid criteria, for a field having a specific data type. Even looking only at the simple examples above, this isn't trivial.

Somebody in the early development team of Access made a good decision, namely to expose the internal (massively complex) routine used to evaluate expressions to the application developer. As the help file states, the function basically simulates what happens when a user types an expression in the query design grid. The routine is available not only at design time but also in the run-time engine, because the advanced filter feature uses a query design grid.

Let's see some examples, and I urge you to open your immediate window to try your own variations (the snippets are formatted to be used in that window).
? BuildCriteria("[field]", dbInteger, "108")                     ' [field]=108
                      ? BuildCriteria("[field]", dbInteger, ">12 and <=20")            ' [field]>12 And ‹field›<=20
                      ? BuildCriteria("[field]", dbInteger, "between 1 and 10")        ' [field] Between 1 And 10
                      ? BuildCriteria("[field]", dbInteger, "in (2,3,5,7,9)")          ' [field] In (2,3,5,7,9)

Open in new window

These four examples are promissing. However, when the input cannot be evaluated, the function either produces an error message, or reverts to a text predicate.
? BuildCriteria("[field]", dbInteger, "><12")                    ' ** invalid syntax **
                      ? BuildCriteria("[field]", dbInteger, "in (1,2,3")               ' ** missing parenthesis **
                      ? BuildCriteria("[field]", dbInteger, "from 1 to 10")            ' [field]="from 1 to 10"
                      ? BuildCriteria("[field]", dbInteger, "betwen 1 and 2")          ' [field]="betwen 1" And [field]=2
                      ? BuildCriteria("[field]", dbInteger, "!=108")                   ' "!"=108

Open in new window

When the criteria reverts to text, this will cause an error when we apply the filter, so we can catch the error later. But the last line is troublesome. The predicate is a valid SQL expression that will simply be evaluated to False. No error, but no records either.

Let's look at a few more examples, using other data types.
? BuildCriteria("[field]", dbText, "A*")                         ' [field] Like "A*"
                      ? BuildCriteria("[field]", dbText, "like Gen[èe]v[ea]")          ' [field] Like "Gen[èe]v[ea]"
                      ? BuildCriteria("[field]", dbText, "between A and M and not K")  ' [field] Between "A" And "M" And Not [field]="K"
                      ? BuildCriteria("[field]", dbText, "NY or LA")                   ' [field]="NY" Or [field]="LA"
                      ? BuildCriteria("[field]", dbText, "who, me?")                   ' ** syntax error **
                      ? BuildCriteria("[field]", dbText, "rock and roll")              ' [field]="rock" And [field]="roll"
                      ? BuildCriteria("[field]", dbText, "B&W")                        ' [field]="B" & "W"

Open in new window

Again, the last two examples will fail (in that no records will match) without throwing an error. This problem needs to be addressed.
? BuildCriteria("[field]", dbDate, "1 aug")                      ' [field]=#8/1/2011#
                      ? BuildCriteria("[field]", dbDate, "> april 2000")               ' [field]>#4/1/2000#
                      ? BuildCriteria("[field]", dbDate, "between 1 jan and 31 dec")   ' [field] Between #1/1/2011# And #12/31/2011#
                      ? BuildCriteria("[field]", dbDate, "02/29")                      ' [field]=#2/1/2029#

Open in new window

The results shown in the comments are those expected using US regional settings. On the machine I'm writing this, they all fail (I would have to use French month names). The last is interpreted as meaning the 1st of February 2029, which is wrong on many levels. As a matter of fact, at least one of the previous examples might already have failed on some machines, but more about that later.

Anyway, with this last batch, it's obvious that some feedback is needed. After update, the text box should be rewritten to show how the criteria has been interpreted. The user types “B&W”, which becomes “ "B" & "W" ”. That looks weird and is easily corrected to “ "B&W" ” (with quotes, preventing any further interpretation of the content).



Looking for Feedback


After entering an expression in the query design grid, the expression is rewritten, showing clearly how the input was interpreted. This is a vital part of the feature. Without some feedback, the user has no clue why no records or the wrong records are being selected.

So, where is it?

Surely, since BuildCriteria is exposed, the corresponding `build criteria feedback' must be as well! Sadly, I didn't find it. There is no additional argument to the function that would return the string, there is no companion function to provide it... Somebody in the Access development team is making a bad decision, and making it over and over with every new version. Why provide only one half of a great feature?

You might think it is easy to extract a meaningful feedback string from the generated criteria, but that is because I didn't insist on the way dates are interpreted, and what it means. On this machine, I need to enter French month names, “April” is not understood. On a normal Swiss-French computer, there would be some more errors. “In (1,2,3)” is a syntax error, because the default list separator is the semi-colon. The expression has to use local separators, but the resulting predicate will use commas. On the other hand, I could write “Dans (1;2;3)”, or “entre 1 et 10 et pas 5” (“between 1 and 10 and not 5”).

To remember: The expression fed into BuildCriteria needs to follow the locale settings (decimal separator, list separator, month names). It may use local keywords (in French: oui, pas, et, comme, etc.), but the returned expression follows American regional settings. This means that the criteria returned by BuildCriteria isn't necessarily accepted as input by the same function.

To transform the returned predicate into something that can be fed again to BuildCriteria, it needs to be localized. It's also important to remove the field name, and it's desirable to localize the most popular keywords. For example:
1 avril ou 15 mai —› fed into BuildCriteria (French version)
[field]=#4/1/2011# Or [field]=#5/15/2011# —› remove field name
#4/1/2011# Or #5/15/2011# —› localize dates
#1.04.2011# Or #15.05.2011# —› localize keywords (optional)
#1.04.2011# ou #15.05.2011# —› remove #? (optional)
1.04.2011 ou 15.05.2011 —› feedback to user...
The localization of the keywords may be optional technically (in the sense that BuildCriteria understands both local and US keywords), but many users would not be happy at all to see the application understanding yet not forgiving their French...


Problem Summary

The BuildCriteria function is technically perfect. The interpretation of user input is as good as it gets, the error messages are often explicit and helpful, and the resulting predicate is always syntactically correct. But — naturally — it sometimes isn't what the user intended.

Implementing a free-text criteria input requires a feedback mechanism, showing the user how the input was interpreted. In a highly technical context, the raw SQL predicate can be displayed, but in most real-life applications, it needs to be translated back into something the user could have written, but without any ambiguity. Alas, although this feature exists in Access, it isn't exposed (or I haven't yet found it).

Translating the predicate to a user friendly feedback string — one that yields the same predicate as the original input when fed to BuildCriteria — requires at least the localization of dates and separators. To further localize keywords, special language-specific routines are needed.



The Filter Form

test and demo formThe form builds upon the samples described in the previous articles. The controls on the left are used as explained there, while those on the right implement the BuildCriteria function. Various inputs are being tested, and the complete resulting filter is displayed at the bottom.

To avoid confusion, this snapshot was taken with custom locale settings. They differ from US settings only in the date format. The control shows an ISO date, and the filter contains the date in US format. Note that SQL also honours ISO dates, but that isn't what BuildCriteria returns.

For predictable user interaction, it is important to validate the input while it happens. Each field's `before update´ calls a function, ValidBC(), handling the errors. Messages issued by BuildCriteria are displayed to the user, and additional checks ensure that the predicate will not raise an error when it's actually applied to a source table.
Function ValidBC( _
                          ByVal Field As String, _
                          ByVal FieldType As Integer, _
                          TextBox As TextBox, _
                          Domain As String _
                          ) As Boolean
                      '
                      ' Validates user input as criteria for a field.
                      ' Calls BuildCriteria() to obtain error messages or a valid predicate,
                      ' which is tested against the domain.
                      ' On success, the predicate is translated back to the application language,
                      ' respecting the user's decimal and list separators.
                      '
                          Static qdf As QueryDef
                          Dim strCrit As String
                          Dim strExpr As String
                          
                      On Error GoTo Failure
                          
                          If IsNull(TextBox) Then
                              gvarFeedbackValidBC = Null
                              ValidBC = True
                              Exit Function
                          End If
                          
                          ' add square brackets if missing and try
                          If InStr(Field, "[") = 0 Then Field = "[" & Field & "]"
                          strCrit = BuildCriteria(Field, FieldType, TextBox.Value)
                          
                          ' the syntax is correct, but does it work as criteria?
                          If qdf Is Nothing Then _
                              Set qdf = CurrentDb.CreateQueryDef("", "SELECT 1;")
                          qdf.SQL = "SELECT 1 FROM " & Domain & " WHERE " & strCrit
                          qdf.OpenRecordset
                          
                          ' BUILDING FEEDBACK
                          ' recalc criteria with Tab as field name, and clean it out
                          strExpr = BuildCriteria(Chr(vbKeyTab), FieldType, TextBox.Value)
                          strExpr = Replace(strExpr, Chr(vbKeyTab) & "=", "")
                          strExpr = Replace(strExpr, Chr(vbKeyTab) & " ", "")
                          strExpr = Replace(strExpr, Chr(vbKeyTab), "")
                          
                          ' call localization function
                          strExpr = Localize(strExpr)
                          
                          ' use one of two sanity checks:
                          ' raise error if expression no longer yields the same predicate
                          If strCrit <> BuildCriteria(Field, FieldType, strExpr) Then _
                              Err.Raise 2431, "Validate", "Invalid syntax."
                          ' or just assert as design time
                          Debug.Assert strCrit = BuildCriteria(Field, FieldType, strExpr)
                          
                          ' store feedback in global variable and exit
                          gvarFeedbackValidBC = strExpr
                          ValidBC = True
                          Exit Function
                          
                      Failure:
                          MsgBox Err.Description
                          Err.Clear
                          Exit Function
                          
                      End Function

Open in new window

As can be seen, the top of the function is quite simple: the criteria is calculated and tested against the table (this traps the error “data type mismatch”). In order to build the feedback string, the field name is stripped from the expression, and passed to the localization function. If all went well, the final local expression should create the same predicate as the original user input. If not, it can be considered a syntax error — which is rather unfair if it's really due to a bug in the function!
Private Function Localize(pstrExpr As String) As String
                          
                          Dim strLocal As String
                          Dim strC As String
                          Dim strToken As String
                          Dim strDecSep As String
                          Dim strLstSep As String
                          Dim fKeywords As Boolean
                          Dim i As Integer
                          Dim p As Integer
                          
                          ' get user's default decimal and list separators
                          strDecSep = winGetDecimalSeparator
                          strLstSep = winGetListSeparator
                          ' (de)activate keyword translation based on UI language
                          Select Case LanguageSettings.LanguageID(2)
                          Case 1036, 4108   ' French and Swiss-French
                              fKeywords = True
                          End Select
                          
                          i = 1
                          Do While i <= Len(pstrExpr)
                              strC = Mid(pstrExpr, i, 1)
                              Select Case strC
                              
                              Case """", "'", "["
                                  ' don't mess with strings and protected names
                                  If strC = "[" Then strC = "]"
                                  p = InStr(i + 1, pstrExpr, strC)
                                  If p = 0 Then Exit Do
                                  strLocal = strLocal & Mid(pstrExpr, i, p - i + 1)
                                  i = p
                                  
                              Case "#"
                                  ' localize date literal
                                  p = InStr(i + 1, pstrExpr, strC)
                                  If p = 0 Then Exit Do
                                  strC = Mid(pstrExpr, i + 1, p - i - 1)
                                  If IsDate(strC) Then strC = Eval("#" & strC & "#")
                                  strLocal = strLocal & "#" & strC & "#"
                                  i = p
                                  
                              Case "."
                                  ' convert to local decimal separator
                                  If IsNumeric(Mid(pstrExpr, i + 1, 1)) Then strC = strDecSep
                                  strLocal = strLocal & strC
                                  
                              Case ","
                                  ' convert to local list separator
                                  strLocal = strLocal & strLstSep
                              
                              Case "a" To "z"
                                  ' obtain full token (alphanumeric)
                                  strToken = strC
                                  Do While i <= Len(pstrExpr)
                                      strC = Mid(pstrExpr, i + 1, 1)
                                      If Not strC Like "[a-z_0-0]" Then Exit Do
                                      strToken = strToken & strC
                                      i = i + 1
                                  Loop
                                  
                                  If fKeywords Then
                                      ' substitute French keywords
                                      Select Case strToken
                                      Case "True":    strToken = "Vrai"
                                      Case "False":   strToken = "Faux"
                                      Case "Yes":     strToken = "Oui"
                                      Case "No":      strToken = "Non"
                                      Case "Is":      strToken = "Est"
                                      Case "Not":     strToken = "Pas"
                                      Case "And":     strToken = "Et"
                                      Case "Or":      strToken = "Ou"
                                      Case "Like":    strToken = "Comme"
                                      Case "Between": strToken = "Entre"
                                      Case "In":      strToken = "Dans"
                                      End Select
                                  End If
                                  
                                  strLocal = strLocal & strToken
                                  
                              Case Else
                                  strLocal = strLocal & strC
                                  
                              End Select
                              i = i + 1
                          Loop
                          If i <= Len(pstrExpr) Then strLocal = strLocal & Mid(pstrExpr, i)
                          Localize = strLocal
                          
                      End Function

Open in new window

Localize() is a very basic expression parser and tokenizer. Characters are read one at a time and special action is taken for some of them: strings and “protected” words in square brackets are left intact; date literals are converted from US to local date format; list separators are replaced with the local choice; decimal separators are identified and replaced; tokens are discovered and extracted (a token is any alphanumeric string starting with a letter).

Depending on the User Interface language, these tokens can be translated. The real built-in function goes even further than the code above: many functions have local names as well. Although this is nice, it really isn't needed for a real life application using BuildCriteria.

Users will mostly stick to very simple criteria, greater or lesser than, perhaps the occasional “Or” and “In ()”. Should users be skilled enough to write an expression using “DansChaîne”, they should be able to accept the translated “InStr”.


In the end, although the missing second half of the feature was a huge disappointment, a few hours of coding produced something that is usable in small applications, and strictly English applications. BuildCriteria remains impractical for any large international deployment, because it's silly to rewrite translation tables for hundreds of languages. If some kind soul at Microsoft were to publish a reliable entry point for these tables in the language DLLs or a undocumented way to expose the feedback mechanism, this would be different.

To test the demo form thoroughly, you will need to at least experiment with various separators and ideally run the demo from a non-English version of Access. Naturally, if you develop for English speaking users only, the code in this article could allow you to use BuildCriteria sensibly.



Conclusion


This concludes the short series of articles about filtering using forms. I had planned it for quite some time, but hadn't thought about breaking it up into smaller chunks. Some of the things I wanted to explain were meant for novice users, while others, like the content of the current article, were clearly not suitable for the same readers. Taking a graded approach and writing three increasingly technical articles solved the issue.

I would like to thank SowleMan one more time. His recent question about “acceptable parameters for BuildCriteria” gave me the motivation to finally publish this series. When he offered to proof-read my article, he certainly wasn't expecting three of them, but that didn't detract him. He diligently corrected many mistakes in my first drafts and offered valuable suggestions to make the text more understandable to all readers.

Rereading them in sequence, I realise that they contain perhaps a dozen “tips and tricks” gathered in almost two decades of programming, and this would have been another possible form of publication. As it stands, some useful bits of information might be buried too deep in the text, and are difficult to find when needed.

I do hope some of this was useful and that you found what you were looking for in the end.


Good luck with your filters!

Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
5
10,185 Views

Comments (0)

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.