I have a query that will display jobs lists according to street name and city (which I key the city and street parameter separately at prompt) but I don't want to have to enter the entire street name seeing how some records are entered as rd. and others road. I just want to be able to query all records that begin with Main (for main st.). Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query street name 1
- Thread starter ryd777
- Start date
in your criteria section of your query and can use the LIKE statement with wildcards like so:
LIKE "ma*"
see
"Using Wildcard Characters in String Comparisons"
"Like Operator"
"About using wildcard characters to search for partial or matching values "
in the Access Help files for more info
HTH
LIKE "ma*"
see
"Using Wildcard Characters in String Comparisons"
"Like Operator"
"About using wildcard characters to search for partial or matching values "
in the Access Help files for more info
HTH
Soundsmith
Programmer
We receive records from the state with the following fields:
lastname, firstname, st_nbr, st_dir, st_name, st_suffix, apt
Smith William 223 Main St
Jones Thomas 1324 W Laurel Dr 6B
Wilson Edna 3212 NW Martin St D
It's more of a pain to fill in, but it can be completely unambiguous-allow only N,S,E,W, NW, NE, SE,SW in dir, no periods in st_suffix. They'll still foul it up in Street name, but this is a training issue. At least you can query on Street name...
David 'Dasher' Kempton
The Soundsmith
lastname, firstname, st_nbr, st_dir, st_name, st_suffix, apt
Smith William 223 Main St
Jones Thomas 1324 W Laurel Dr 6B
Wilson Edna 3212 NW Martin St D
It's more of a pain to fill in, but it can be completely unambiguous-allow only N,S,E,W, NW, NE, SE,SW in dir, no periods in st_suffix. They'll still foul it up in Street name, but this is a training issue. At least you can query on Street name...
David 'Dasher' Kempton
The Soundsmith
RegionsRob
Programmer
ToeShot gave the method I use. Except things can get further complicated when you have multi-word street names. Some users will key "Saint James Place", "St James Place", "St. James Pl", etc. (You're right, Soundsmith, those pesky users never stick with the way we developers designed the field. 
)
Therefore, I always use a LIKE statement with asterisks around both sides of the search string.
[tab]LIKE "*James*"
)Therefore, I always use a LIKE statement with asterisks around both sides of the search string.
[tab]LIKE "*James*"
-
1
- #5
You may want to include spaces in the criteria field so that a search for "Rand" doesn't return "Grand",
"Randall", "Brand", etc... (real streets where I live).
Like "*" & " " & [Enter STreet] & " " & "*"
I parse the street names out of the address strings whenever possible and use a combo box with a distinct select. You should be able to already use the combo box for City names if it's a separate field.
HTH
John
Use what you have,
Learn what you can,
Create what you need.
"Randall", "Brand", etc... (real streets where I live).
Like "*" & " " & [Enter STreet] & " " & "*"
I parse the street names out of the address strings whenever possible and use a combo box with a distinct select. You should be able to already use the combo box for City names if it's a separate field.
HTH
John
Use what you have,
Learn what you can,
Create what you need.
- Thread starter
- #7
- Status
Similar threads
- Locked
- Question
- Locked
- Question