Help: Need 1 criteria to find many parts of 1 field

[ROULA]

Hi,
Please I need to find in a querry in 1 field multiple parts, e.i in the
field there is "Joseph Smith" I want when i search in my querry "Jo Smi" to
find this record.
Any HELP PLSSSSSSSS

 

[Bob Barrows [MVP]]

Hi,
Please I need to find in a querry in 1 field multiple parts, e.i in
the field there is "Joseph Smith" I want when i search in my querry
"Jo Smi" to find this record.
Any HELP PLSSSSSSSS

WHERE Fieldname LIKE 'Jo*Smi*'

The only problem with this is it will find all rows where this pattern is
satisfied:
Josephine Smithers, John Smith, JoAnne Jasmine, etc.

 

[BruceM]

It would be best by far to have the name in two fields. The criteria for
FirstName would be:
Like [Enter First Name] & "*"
Similarly, for the LastName field:
Like [Enter Last Name] & "*"

With your current set-up you could try a criteria such as:
Like [Enter First Name] & "*" & [Enter LastName] & "*"

I don't think you can parse a string such as you wish to use, at least not
as a query criteria. You could probably do it in VBA code, but it would be
clumsy at best. You would need to parse the search string into two parts,
then create a SQL string that use the parts similarly to how the two
criteria are used in my second suggestion along with the rest of the
necessary fields, then use the SQL string as the recordset. In similar
fashion you may be able to create a filter string instead of a recordset
string, but in either case it is a lot of work to overcome the limitations
of an ineffective design.

 

[John W. Vinson]

Hi,
Please I need to find in a querry in 1 field multiple parts, e.i in the
field there is "Joseph Smith" I want when i search in my querry "Jo Smi" to
find this record.
Any HELP PLSSSSSSSS

That's not going to be easy. Do you REALLY need to type in - what - an
arbitrary number of characters at an arbitrary location anywhere within the
text string, and have Access automagically figure out that you want "jo" to be
one search term and "smi" to be another search term?

You can use a criterion

LIKE "*jo*" AND LIKE "*smi*"

or more generally

LIKE "*" & [Enter one search term:] & "*" AND LIKE "*" & [Enter second search
term:] & "*"

to be prompted twice; if you enter JO in response to one prompt and SMI in
response to the other, you'll find Joseph Smith (and Smiley Johnson).