[Prompt] data as filter for criteria

[hermhart]

I want to enter a string expression as criteria for a field, in order to filter the records of a query. I want to create a prompt to allow the user to provide the input. The prompted input will then be a PORTION of the filter expression. OK.
1. If the prompted input is, in its entirety, the filter expression, that's simple. The expression is simply the prompt inside square brackets.
e.g. -> [Please enter something]
Returned will be the records for which the data in the given field matches the input information.
2. If I want to indicate only a PORTION of the total data to filter for, and I put this portion into the filter, that's also simple.
e.g. -> Like "*met*"
will return all records with <met> anywhere in the field, such as Calumet, Metropolitan, New York Mets.
But I want the combination of these two.
If I enter -> Like "*[met]*", everything between the ** is treated as mere characters and the brackets' contents are not understood to be prompt text. But *[met]* by itself is invalid syntax.
So I'm rather stuck.
Thanks

[xordevoreaux]

I have a table named table1. My field in table1 is called [name]. (bad choice, but eh).

In my query, I create the following field:

Exists: IIf(InStr(1,[table1].[name],[Enter Something],1)>0,1,0)


I set the criteria of this field to 1.

I then include in a column next to that field my [name] field.

I had as values in my table the following:

Pizza
Metropolitan
New York Mets
Kismet

When I entered pi when prompted, the returned result was the row containing "pizza"

[hermhart]

Hey, is that cool or what!!

[xordevoreaux]

Glad it worked