How assume wildcards in a query criteria

[Clay]

I have an access 2003 database for which I have created a very simple lookup
query. I use a form to call the query and I use the "Like [INPUTFieldName]"
criteria. So when someone clicks the button on this form it prompts them to
type in some text and the query searches for relevant results.

This lookup is done on a name field that includes first, last, title, etc.
I would like for the users of this DB to be able to just type in first and
last name and get all relevant results. But I do not know how to do this
without having them use wildcards. So it the persons name is Joe Smith, we
type in *joe*smith* to get all fields with either Joe or Smith in them.
Otherwise the query would not return "Joe Smith II" or "Joe Smith JR.". I
need to know how to fix the query so that it will assume these wildcards. I
would even like for it to ignore the order of the names so it would return
"smith, joe" or "joe smith" with the same query (all rows with smith or joe
in them no matter which comes first).

thanks in advance for any assistance,
Clay

 

[Clay]

Okay I have found this much - if I use this criteria:
Like [Enter Name Here] & "*"
I get the wildcard for one input string (either first or last name). But I
still don't know how to get it to search for first and last name from the
same input field. I have tried several other combinations using the & "*"
but have not gotten it yet.

 

[Dale Fye]

Clay,

Since you are already using a form with a command button, I would recommend
that you expand on that and add a textbox to the form. Then, you can put
some code in the Click event of the command button to parse the text in the
textbox (use the Split( ) function, and create a criteria clause that imbeds
the * in your query, so that when they type "Joe Smith" in the textbox,
clicking on the command button would result in a criteria that looks like:

[NameField] Like '*Joe*' OR [NameField] Like '*Smith*'

You didn't indicate whether this information is to be used to filter the
current form (if so, I'd put the unbound textbox in the forms header or
footer), or whether it is for a report or to use for another control on this
or another form, so what you do with that criteria will be based on what you
need it form.

If you need more info on how to implement this, post back.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Clay,

Since you are already using a form with a command button, I would recommend
that you expand on that and add a textbox to the form. Then, you can put
some code in the Click event of the command button to parse the text in the
textbox (use the Split( ) function, and create a criteria clause that imbeds
the * in your query, so that when they type "Joe Smith" in the textbox,
clicking on the command button would result in a criteria that looks like:

[NameField] Like '*Joe*' OR [NameField] Like '*Smith*'

You didn't indicate whether this information is to be used to filter the
current form (if so, I'd put the unbound textbox in the forms header or
footer), or whether it is for a report or to use for another control on this
or another form, so what you do with that criteria will be based on what you
need it form.

If you need more info on how to implement this, post back.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.