First Published 25 May 2024                                                Difficulty level :   Moderate


Introduction

This article discusses differences in outcomes for various filters performed using 'Like ...' in the query designer and the two right click context menu 'Contains' items.

I am now calling this the 'Batlle bug' in acknowledgement of my Spanish developer colleague Xevi Batlle who first discovered the issue and brought it to my attention.

Xevi found that Query Contains filters using logical operators " and ", " or " or their Spanish equivalents " y ", " o " give confusing or incorrect results when used with leading and trailing spaces. Other developers have since confirmed issues with the localised equivalents of " and " & " or " in their own languages.

I did further tests which indicated that the issue goes far beyond just those logical operators and applies to many (but not all) special characters including " = ", " < ", " >"

The Access team have acknowledged the issues and are currently considering the best way to resolve these.



Test Details

I used a test table of 32 records containing a mixture of alphanumeric and special characters designed to test outcomes for a wide range of characters


There are two ways of using the Contains filter in tables, queries and datasheet forms. The two methods SHOULD both give identical results!
For example, to filter for contains " and ", you can:

1.   select the filter text you want to use on any suitable record, then right click and select Contains. For example:


2.   right click on a field header, select Text Filters . . . Contains and then enter the filter text in the Custom filter input box.

Unfortunately, there are many situations where these two methods give VERY different results.

Method 1 appears to ALWAYS work correctly. The results are IDENTICAL to those obtained using the query designer.

The 'Batlle bug' issue is with method 2, where the parser in many cases gets the filter completely wrong

In all the examples below:
1.   Query Designer - refers to the results using method 1 (highlight contains) or entering filter criteria in the query designer window
2.   Contains filter - applies to the results obtained using method 2 (Text Filters . . . Contains) then typing the filter string



a)   Contains "and"

      Filtering for Contains "and" with no spaces works correctly. It is always equivalent to using Like "*and*" in the query designer.

1. Query Designer / 2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "*And*")); Records:     2

b)   Contains " and "

      Filtering for Contains " and " (with leading & trailing spaces) should also be equivalent to using Like "* and *" in the query designer.
      However, in this case different results are obtained.

      The query designer correctly gives 1 record: "A and B" but the Contains filter returns all 32 records.

1. Query Designer	2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "* And *")); Records:     1	Filter SQL:  WHERE (((tblTest.txtText) Like "*" And (tblTest.txtText) Like "*")); Records:     32

      To determine the reason for the discrepancy, we need to view the SQL for the Contains filter

      As context menu filters are not usually saved, the method required to view the SQL is rather convoluted. To do so:
      1.   Apply the filter that gives incorrect results
      2.   On the Home ribbon, click Sort & Filter...Advanced...Advanced Filter / Sort


      3.   Right click on the query window generated and select Save As Query


      4.  Open the saved query in SQL view

      In the case of the Contains " and " filter, the generated SQL is

SELECT * FROM tblTest
WHERE (((tblTest.txtText) Like "*" And (tblTest.txtText) Like "*"));

      The WHERE filter Like "*" And Like "*" is incorrect. It returns all records as "*" is a wildcard



c)   Contains "or"

      Filtering for Contains "or" with no spaces also works correctly. It is equivalent to using Like "*or*" in the query designer.

1. Query Designer / 2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "*or*")); Records:     6

d)   Contains " or "

      A similar discrepancy occurs using the logical operator " or "

      The query designer filter for Like "* or *" returns 1 record: C or D

      Contains " or " again returns all 32 records as the generated SQL is incorrect:

SELECT * FROM tblTest
WHERE (((tblTest.txtText) Like "*" Or (tblTest.txtText) Like "*"));

1. Query Designer	2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "* Or *")); Records:     3	Filter SQL:  WHERE (((tblTest.txtText) Like "*" Or (tblTest.txtText) Like "*")); Records:     32

e)   Contains "not" / Contains " not "

      By contrast, filtering for Contains "not" works correctly with or without spaces. These are equivalent to using Like "* not *" or Like "*not*" in the query designer.

Like "*not*" 1. Query Designer / 2. Contains filter	Like "* not *" 1. Query Designer / 2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "*not*")); Records:     3	Filter SQL:  WHERE (((tblTest.txtText) Like "* not *") ; Records:     1

      So why does the logical operator 'not' always behave correctly even with spaces? I've absolutely no idea!



f)   Non-English language equivalents

      Using any non-English language Office gives exactly the same results as above for the English operator words 'and', 'or' and 'not' (with and without spaces).
      Each of these act as 'universal expressions' in any Office language

      However, in other languages, the localised expressions for 'and', 'or'and 'not' also exhibit the same behaviour as the English equivalents.

      For example in Spanish, ' y ' is equivalent to ' and '

1. Query Designer	2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "* y *")); Records:     2	Filter SQL:  WHERE (((tblTest.txtText) Like "*" y (tblTest.txtText) Like "*")); Records:     32 (Spanish) ; 2 (English)

      Similarly, in Spanish ' o ' is equivalent to ' or '

1. Query Designer	2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "* o *")); Records:     1	Filter SQL:  WHERE (((tblTest.txtText) Like "*" o (tblTest.txtText) Like "*")); Records:     32 (Spanish) ; 1 (English)

g)   Other incorrect results using Text Filters . . . Contains

      In my tests, incorrect values were also obtained using several other characters in Filter . . . Contains. Discrepancies occurred with:
            "A and B"    Did not get value 'A and B'
            "C or D"      Did not get value 'C or D'
            " ! "             Wildcard - but not as used here
            " & "
            " " "
            " + "
            "+"
            " < "
            " > "
            " . "
            " , "
            " ( "
            " ) "
            " = "
            " [ "

      For example, these are the results for Contains "C or D":

1. Query Designer	2. Contains filter
Filter SQL:  WHERE (((tblTest.txtText) Like "C or D")); Records:     1	Filter SQL:  WHERE (((tblTest.txtText) Like "*C" Or (tblTest.txtText) Like "D*")); Records:     3

      This results summary is taken from the Excel file available in the Downloads section below:


      See the test database for full details



h)   Correct results using Text Filters . . . Contains

      Thankfully, there were also several characters that do appear to give correct results. Several involving wildcards are possibly open to interpretation
            " ? "      Wildcard for single character
            " * "      Wildcard - in this case for space
            " # "      Unexpected results - wildcard for number with surrounding spaces
            "#"        Wildcard for any numerical character
            "[ ]"      Wildcard for any single character inside brackets (space or M)
            "[ M ]"  Wildcard for any single character inside brackets (space or M)
            "[M]"    Wildcard for M
            " ' "
            " ¬ "
            " % "
            " @ "
            " - "
            " ~ "
            " / "
            " \ "
            " ^ "
            " ] "
            " { "
            " } "
            " : "
            " ; "
            " | "
            " _ "
            " ` "

      This results summary is also taken from the Excel file available in the Downloads section:


      Once again, see the test database for full details


Downloads

      The zip file contains the test ACCDB file, the Excel summary and a Word doc summarizing the various Access filter wildcards

      Click to download:       Query Filter Contains Bug       Version 5A      Approx 0.22 MB  (zipped)

      Download the zip file and unblock it. For more information, see my article: Unblock downloaded files by removing the Mark of the Web

      Unzip and save the ACCDB file to a trusted location.



Summary

Currently, using method 2 for the Contains filter is too often unreliable and gives confusing or completely wrong results.
For now, I would recommend double checking outcomes or avoiding that approach completely.

The Access team have acknowledged this as an issue and are currently looking into the best solution for the problem.
However, where possible they also want to do so without breaking existing 'working behaviour'

Whilst I understand the reluctance to potentially break existing behaviour, I very much doubt this is a real issue here.
Normally users / developers save queries either as SQL or using the query designer.

Filters applied using the context menu are typically ephemeral and I think will rarely be saved.
In over 25 years working with Access, I have hardly ever saved the results of the context filter using the method described in this article to demonstrate the incorrect SQL.
I suspect the vast majority of developers would say the same.

I think that far more people are likely to be affected by confusing or clearly incorrect results such as those demonstrated by the ‘Batlle bug’ in this article

The most important requirement is for filters to give consistent and correct results no matter which method is used.

I very much hope that the changes required to achieve this will be relatively simple and quick for the Access team to implement.



Feedback

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 25 May 2024

Return to Access Articles Page

Return to Top