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

That's even better, Grover

I used it in the criteria for the query as plaintext([Meaning]) like "T*" and voila! 26 records returned. That saves having a field to duplicate the contents of another, so saves space - not that I am ever likely to go beyond 10,000 records!

Thanks very much for taking the time and trouble to reply.

Regards

Dave