MS Access query using like "*T" does not return all records

Question

Hello all

I have a fair amount of experience in Access, VBA and SQL, but for some reason, I am experiencing a very strange phenomenon. I have a single table (so no relationship issues) with only four fields, which I use to help me learn a new language. One field is the foreign word, the other is the translation. 

As you can see, one of the rows contains the translation for the word Toothbrush. The 'Meaning' field is set as long text, rich text format.

Now in the query, I have used the like "T*" to try bring out the matches, but it misses several. Here is the syntax of the (very simple) query.

When I run the query, I get the following results:

And lo and behold, NO toothbrush! I have checked there are no spaces at the start of the field. I even tried deleting the meaning out and re typing it to be sure. The only way I can get it to work is by using like "*T*" - which returns ANY meaning with a letter T anywhere within the string, so not really what I want either. I can't even get it to return the record when I type ="Toothbrush", so it is like there is some hidden character in the field.

Any advice gratefully received.

Regards

Dave

Answer 1

You might also find this article on PlainText() useful.

Answer 2

You might try applying a trim() function to the field and applying the criteria to that.

Answer 3

Hi Daniel

Thanks for your suggestion. I tried that and unfortunately, it yields 0 records as opposed to the 11 I get using the like "T*" criterion. I thought trim only removed blank spaces which are visible and the original field doesn't have any leading spaces.

Regards

Dave

Answer 4

That is indeed odd. I would have thought that a) Daniel's suggestion would be effective, or b) completely removing the original record and adding a new one with the offending values would resolve the problem.

I notice, though, that you say adding Trim() returned 0 records. I wonder if you did that this way:

Trim([Meaning]) Like "T*"

What is the result of adding this as an additional column in the query:

Len([Meaning]) as CharactersinThisField

Does the count returned match what you see in that field in that record?

Answer 5

Hi Grover

Thanks for the tip, I was using TRIM incorrectly - but using it as you showed still only returns 11 records. I then did what you suggested and extracted the length of the fields. Curiouser and curiouser! I created a new record and gave the word Fred with the meaning Toothbrush. Still no matches with like "T*" or trim ([Meaning]) like "T*". However, if I user like "*Tooth*" I get the following:

The original it estimates the length of the word Toothbrush as 50 characters, and then the NEW record with no adornments, it still calculates as 21 characters. It is as though it is somehow applying formatting characters somewhere in the string. It isn't something I have seen before :(

Regards

Dave