query to search for street name in address field

[nancee]

I have a table that has a street name field it contains an address like 123
Main St. I want to know how to make a query that would look for street names
beginning with "m."

 

[Duane Hookom]

Does the street name always follow the first space? If so, you could use a
WHERE clause like:

WHERE Mid([StreetName],Instr([StreetName]," ")+1,1) = "m"

 

[vanderghast]

LIKE "* m* st.*"

assuming there will always be a space_s_t_dot after the street name.

That is to be used as criteria and it will return the record WHERE there is
a street name starting with the letter m, NOT the street name
itself, that
is.

It also assumes there is at least a space in front of the street name.


Vanderghast, Access MVP

 

[nancee]

Thanks for all your help but I'm still not getting what I need. Here is the
exactly what I'm trying to do:

I am trying to find street addresses that begin with a "w." So in my query it
should bring up

222 West Portal Ave
44 Wayaguez Blvd
237 W. 77th Street
378 Wildwood Street

But instead I get like Main Ways and Alwood Street.

 

[Duane Hookom]

Did you try:
WHERE Mid([StreetName],Instr([StreetName]," ")+1,1) = "w"

 

[nancee]

Yes I did but I get an error saying "Data type mismatch in criteria
expression"

Did you try:
WHERE Mid([StreetName],Instr([StreetName]," ")+1,1) = "w"

Thanks for all your help but I'm still not getting what I need. Here is the
exactly what I'm trying to do:

[quoted text clipped - 26 lines]

 

[Duane Hookom]

What is the full SQL view of your query?

Are there records where StreetName might be either null or not contain any
spaces? If so, try:
WHERE Mid([StreetName] & " " ,Instr([StreetName] & " "," ")+1,1) = "w"


--
Duane Hookom
Microsoft Access MVP

Yes I did but I get an error saying "Data type mismatch in criteria
expression"

Did you try:
WHERE Mid([StreetName],Instr([StreetName]," ")+1,1) = "w"

Thanks for all your help but I'm still not getting what I need. Here is the
exactly what I'm trying to do:

[quoted text clipped - 26 lines]

names
beginning with "m."