Wildcard search does not work with a form control

[Gkirkup]

I am trying to get a wildcard search to work with a form.
I have a query, in which the criteria is:

Like "*" & [Enter a word] & "*"

That works fine. I enter a word, and I get the few records in which the word appears.
But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:

Like "*" & [Forms]![Myform]![Mycontrol]&"*"

What am I doing wrong?

Robert

 

[Galaxiom]

You need to delimit the string with single quotes.

Like "'*" & [Forms]![Myform]![Mycontrol]&"*'"

 

[Gkirkup]

Thanks. I tried that and it didn't work. It returned nothing. So put a single quote before the first asterisk and after the second asterisk? I can't get that to work. Am I missing something?

Robert

 

[pbaldy]

How are you testing? One possible problem is that the value may not yet be committed in the textbox. Make sure you Tab or Enter after entering the word before running the query.

 

[Gkirkup]

Yes, I am sure that the entry is accepted. I am using a test form with just two controls, and always tab to the second control before running the query. The query returns nothing.
I have Googled this and all the examples I see use a pair of double quotes "*", and not a single quote as well. "'*". But having said that, neither works.
But Like "*"& [Enter a word] &"*" works fine.
Any ideas?

Robert

 

[pbaldy]

Just tested and this worked fine:

Like "*" & [Forms]![zTestForm]![Text1] & "*"

 

[Gkirkup]

Thank you. That is very strange. When I use "*" or '*' I get all the records. When I add a single quote outside the asterisk I get no records.

Robert

 

[Gkirkup]

OK, mystery solved. I made a new query and it worked fine. I have seen that before. A query does not work and I can't solve it. I make a new query exactly the same and it works!

Robert

 

[pbaldy]

Curious. Glad you got it sorted.