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

Ok, so now I am a little bit closer to working out WHY it is happening, just not how to resolve it. It seems that, during developing this, I changed from plain text to rich text format. In doing so, Access appears to wrap <DIV> tags around everything, including the new record. I discovered this by creating a new, short text field as plain text and an update query to copy the contents of the [Meaning] field into it. This is what I see.

Access is adding HTML formatting which is hidden of course, to every new entry. I guess the only way around it is to make the new field a permanent addition, and on update, get Access to strip any HTML tags out, so that I can then query the new field.

Regards

Dave

Answer 3

I have a fair amount of experience with Access too, but next to no expertise. :-( But the first thing that struck me about your search string (filter string) is that I remember that I didn't get a reliable result until I put an asterix before and after the letter. I have a field in my database that contains data like this: "K15-18/T19-20". If I want to find a record where that field has a "T" in it, I am pretty sure I need to use "*T*". I may be way off base for your particular problem, but I thought I'd try to help.

David

Answer 4

Hi David

Thanks for this. The answer is in the fact that the field is now rich text, so pads everything out with <DIV> and </DIV>. Grover gave me the hint, by checking the field length. I have never had to use the like "*T*" with a plain text field before, and now that I have created a second field which converts the rich text to plain text, all works. To do this, I created a second Meaning field, which I called Meang2 and used the application.plaintext(fieldname) to convert it from the [Meaning] field into the [Meang2] field using an afterupdate event.

The reason I wasn't happy with like"*T*" is that it would bring back any record with a T anywhere in the string, rather than just those that begin with the letter T, and I have an option group with each letter of the alphabet, so want just the words that begin with the selected letter. Thanks for offering to help, and I hope this might help you with your database project too :)

Regards

Dave

Answer 5

Good detective work. Congratulations on figuring it out.

I rely on a lot of little trouble-shooting tricks like that to verify that what I see is what is actually there, so to speak.

Continued success with the project.

George