Partial match for query criteria

[BJH712]

Hello,

I am trying to create a query that will deliver results based on matching
only a part of the text in a table field. For example, if I have a table that
contains the names, address and phone numbers of 10,000 businesses but I only
want my query to show me businesses that have the word "National" in their
name, how would I do that? I'm not sure how to set up the criteria.

Thanks for your help!

 

[Ken Sheridan]

Use the Like operator with wildcards. In the 'criteria' row of the business
name column in query design view enter:

Like "*National*"

Or you can use a parameter:

Like "*" & [Enter all or part of business name:] & "*"

You'll then be prompted for the name when the query or any form or report
based on it is opened.

You might get some mismatches if the text you enter is a substring within a
word for instance, e.g. any names containing "international" would also be
included in the result set. To find only distinct words or phrases enter
the following in the 'field' row of a blank column in the query design grid :

" " & "First international bank" & " "

and the following in the 'criteria' row

Like "* " & [Enter all or part of business name:] & " *"

This will find occurrences of the string if preceded and followed by a
space, but if applied to the field itself would miss words or phrases at the
start or end of the field of course, which is why you have to apply it to a
computed column in which spaces are concatenated onto the start and end of
the value in the field.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Create a new query in design view.

Add the [BusinessName] field.

In the selection criterion beneath it, put something like:

Like * & [Enter partial business name] & *

When you run this query, it will prompt for a partial business name, then
search for any record with that string somewhere in the [BusinessName]
field.

Use your real field names. Add any other fields in the query that you want
to see.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BJH712]

Thank you! I knew it couldn't be that difficult. You've just saved me so much
time.

Use the Like operator with wildcards. In the 'criteria' row of the business
name column in query design view enter:

Like "*National*"

Or you can use a parameter:

Like "*" & [Enter all or part of business name:] & "*"

You'll then be prompted for the name when the query or any form or report
based on it is opened.

You might get some mismatches if the text you enter is a substring within a
word for instance, e.g. any names containing "international" would also be
included in the result set. To find only distinct words or phrases enter
the following in the 'field' row of a blank column in the query design grid :

" " & "First international bank" & " "

and the following in the 'criteria' row

Like "* " & [Enter all or part of business name:] & " *"

This will find occurrences of the string if preceded and followed by a
space, but if applied to the field itself would miss words or phrases at the
start or end of the field of course, which is why you have to apply it to a
computed column in which spaces are concatenated onto the start and end of
the value in the field.

Ken Sheridan
Stafford, England

Hello,

I am trying to create a query that will deliver results based on matching
only a part of the text in a table field. For example, if I have a table that
contains the names, address and phone numbers of 10,000 businesses but I only
want my query to show me businesses that have the word "National" in their
name, how would I do that? I'm not sure how to set up the criteria.

Thanks for your help!

 

[BJH712]

Thank you! That was very helpful and it worked perfectly!

Create a new query in design view.

Add the [BusinessName] field.

In the selection criterion beneath it, put something like:

Like * & [Enter partial business name] & *

When you run this query, it will prompt for a partial business name, then
search for any record with that string somewhere in the [BusinessName]
field.

Use your real field names. Add any other fields in the query that you want
to see.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I am trying to create a query that will deliver results based on matching
only a part of the text in a table field. For example, if I have a table
that
contains the names, address and phone numbers of 10,000 businesses but I
only
want my query to show me businesses that have the word "National" in their
name, how would I do that? I'm not sure how to set up the criteria.

Thanks for your help!

 

[Delboy]

Thanks, Just what i was looking for too!