How to refer to a text box with the LIKE operator?

[bassman197]

Hi everyone!

I'm working on creating a small database for the company I work at and just can't figure out the syntax to make this work.

If I create a query that looks for clients Like '*Smith*' , it returns records where the client is John Smith, Smith and Company, Edward Smith & Jones, LLC and that part works great! I did this by typing: Like '*Smith*' in the criteria field for Client in the design view of the query. (Also seems to work whether I use single or double quotes - not sure about the implications of that).

Now, I want to replace the word Smith with whatever a user enters into a text box, I can't get the syntax right! In the design view of the query, I've tried things such as: Like '*forms![Search by Client]![Text0]*' with no luck. Any ideas? Maybe I'm missing some brackets or parenthesis?

 

[boblarson]

Hi everyone!

I'm working on creating a small database for the company I work at and just can't figure out the syntax to make this work.

If I create a query that looks for clients Like '*Smith*' , it returns records where the client is John Smith, Smith and Company, Edward Smith & Jones, LLC and that part works great! I did this by typing: Like '*Smith*' in the criteria field for Client in the design view of the query. (Also seems to work whether I use single or double quotes - not sure about the implications of that).

Now, I want to replace the word Smith with whatever a user enters into a text box, I can't get the syntax right! In the design view of the query, I've tried things such as: Like '*forms![Search by Client]![Text0]*' with no luck. Any ideas? Maybe I'm missing some brackets or parenthesis?

Try this:

Like "*" & [Forms]![Search by Client]![Text0] & "*"

 

[bassman197]

Worked great!

Wow, that was brilliant - worked first try! Thanks so much!

 

[Moniker]

Make sure you understand why what Bob said is right. The variable in your form is just that -- a variable. Pretty much anything can go in there. Therefore, you have to reference it as a variable, not a literal. For example, if you did this:

strName = "Bob".

This would be wrong:

MsgBox "The name is strName"

It would return the literal "The name is strName".

This is right:

MsgBox "The name is " & strName

It would return the variable "The name is Bob".

Anytime you reference a variable, you have to keep it outside the quotes for Access to "look up the reference" as it were.

I'm only bothering to explain this because it's a very common error that newer programmers make. Getting and understanding it early will help in the long run.