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
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):
between 100 and 200
>10 and <=20
7 or 11
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?
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)
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
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"
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#
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”
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
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
The 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
' 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
' 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
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
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
' 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
' convert to local decimal separator
If IsNumeric(Mid(pstrExpr, i + 1, 1)) Then strC = strDecSep
strLocal = strLocal & strC
' 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
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"
strLocal = strLocal & strToken
strLocal = strLocal & strC
i = i + 1
If i <= Len(pstrExpr) Then strLocal = strLocal & Mid(pstrExpr, i)
Localize = strLocal
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.
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
This article is part of the EE-book unrestricted Access