Partial Search in a Query

[Guest]

This may be a very simple answer but i just cannot figure it out. I have a
query with multiple columns one being address. How could i search for only a
portion of whats entered in the column. Below is a sample table:
Name Address
John Smith 600 Main St
Frank Smith 625 Main St
Sam Jones 700 Main St
Lucy Ford 800 Front St
Sam Ford Main St
Lucy Smith Main

How would I run a query for all records that have Main in the Address
column? Every time I run a query it only retrieves the records that only
have exatly what i put down. If I searched for Main it would only retrieve
the last record when I would like it to retrieve all except for the fourth
record. Any help would be great.

 

[Guest]

select * from [YourTable]
where [Address] LIKE "*Main*";

Read up on the LIKE operator and the use of wildcards to do these types of
searches.

Good Luck!

 

[Guest]

I have created some Parameter Queries and what you are suggesting doesn't
seem to work. Does it only work when entered into the Criterea slot or
should it work when the parameter query box pops up?

select * from [YourTable]
where [Address] LIKE "*Main*";

Read up on the LIKE operator and the use of wildcards to do these types of
searches.

Good Luck!
--
Chaim

This may be a very simple answer but i just cannot figure it out. I have a
query with multiple columns one being address. How could i search for only a
portion of whats entered in the column. Below is a sample table:
Name Address
John Smith 600 Main St
Frank Smith 625 Main St
Sam Jones 700 Main St
Lucy Ford 800 Front St
Sam Ford Main St
Lucy Smith Main

How would I run a query for all records that have Main in the Address
column? Every time I run a query it only retrieves the records that only
have exatly what i put down. If I searched for Main it would only retrieve
the last record when I would like it to retrieve all except for the fourth
record. Any help would be great.

 

[Guest]

The SQL looks like:

SELECT * FROM [Address Table]
Where Address LIKE "*" & [Part of Address?] & "*";

In the Query Grid, on the Criteria line enter

LIKE "*" & [Part of Address] & "*"

Good Luck!
--
Chaim

I have created some Parameter Queries and what you are suggesting doesn't
seem to work. Does it only work when entered into the Criterea slot or
should it work when the parameter query box pops up?

select * from [YourTable]
where [Address] LIKE "*Main*";

Read up on the LIKE operator and the use of wildcards to do these types of
searches.

Good Luck!
--
Chaim

This may be a very simple answer but i just cannot figure it out. I have a
query with multiple columns one being address. How could i search for only a
portion of whats entered in the column. Below is a sample table:
Name Address
John Smith 600 Main St
Frank Smith 625 Main St
Sam Jones 700 Main St
Lucy Ford 800 Front St
Sam Ford Main St
Lucy Smith Main

How would I run a query for all records that have Main in the Address
column? Every time I run a query it only retrieves the records that only
have exatly what i put down. If I searched for Main it would only retrieve
the last record when I would like it to retrieve all except for the fourth
record. Any help would be great.

 

[fredg]

This may be a very simple answer but i just cannot figure it out. I have a
query with multiple columns one being address. How could i search for only a
portion of whats entered in the column. Below is a sample table:
Name Address
John Smith 600 Main St
Frank Smith 625 Main St
Sam Jones 700 Main St
Lucy Ford 800 Front St
Sam Ford Main St
Lucy Smith Main

How would I run a query for all records that have Main in the Address
column? Every time I run a query it only retrieves the records that only
have exatly what i put down. If I searched for Main it would only retrieve
the last record when I would like it to retrieve all except for the fourth
record. Any help would be great.

As criteria in the Address field, write:
Like "*" & [Enter Street] & "*"

When prompted for the street, enter Main