Provided by Allen Browne, November 2006
If you have spaces following the last visible character in a field, JET may match your data wrongly. Query results are inconsistent, depending on the operator you use and your version of Access.
To demonstrate the problem, open the Northwind sample database, and run this query to insert a record that has a couple of spaces at the end of the company name:
INSERT INTO Customers ( CustomerID, CompanyName )
SELECT 'ABad1' AS CustomerID, 'A Bad One ' AS CompanyName;
Now try this query, without the spaces in the criteria:
SELECT CompanyName
FROM Customers
WHERE CompanyName = 'A Bad One';
The query returns the record, even though the criteria does not match (i.e. the criteria is shorter by 2 characters.)
Now try Like in place of the = operator:
SELECT CompanyName
FROM Customers
WHERE CompanyName Like 'A Bad One';
This time, the query does not return the record, i.e. it correctly recognised that the criteria string was different than the value in the field. The Like operator behaves differently than the equal operator.
Unless you used Access 97 or earlier! In older versions of Access, the Like operator does return the bad match, i.e. Like gives the same results as the = operator in Access 97 and earlier, but not in Access 2000 and later.
What a mess.
You get the same problem if the criteria value contains the trailing spaces instead of the field. Try this query:
SELECT CompanyName
FROM Customers
WHERE CompanyName Like 'Around the Horn ';
In Access 2000 it returns no match, but in Access 97, it does. Replace the Like with =, and it yields the wrong result in both versions.
The problem can occur with characters other than the space. A trailing null character also gives inconsistent results:
SELECT CompanyName
FROM Customers
WHERE CompanyName = 'Around the Horn' & Chr(0);
In Access 2000, this query does return a match, but in Access 97 it does not. This time the two versions disagree on the answer regardless of whether you use Like or = as the operator.
(Note the the null character is not the same as the Null value. The null character typically ends up in a database as a result of an incorrectly parsed call to an API or COM object.)
Naturally enough, these query results do not match what you get if you compare the field value and a literal value in VBA code.
We used the WHERE clause to demonstrate this flaw, but you get the same inconsistent results in any clause - even when matching fields in a JOIN.
In most cases, trailing spaces represent bad data. Access helps you avoid this by stripping any trailing spaces you enter in a bound control before it is written to the table.
But there are still many ways you can accidentally end up with trailing spaces in your field:
Of course, there are also valid cases when you might want trailing spaces (such as a field that contains boilerplate phrases.)
Use Trim() when parsing or importing. Use TEXT rather than CHAR when creating fields in DDL, and specify the size.
Use the equal operator rather than Like unless you use wildcards. At least this helps avoid the disparity between Access versions.
If you have fields that validly end with spaces, be aware of when the bug will bite.
The bad results do not seem to be affected by whether the field is indexed or has the Allow Zero Length or Unicode Compression properties set. The database's ANSI 92 setting also make no difference.
It makes it hard to trust the results Access gives you when Access 97 gives one answer and Access 2000 gives another, when the equal operator and the Like operator give different answers, and when performing the comparison in a query gives a different answer than the same comparison in VBA.
| Home | Index of tips | Top |