parsing address into house #, Street name, street type

[upstate-dem]

I've been able to take the house # and Street type and put them into their
proper fields. The street name (being anything in between the first and last
" " of the full address) seems to be stumping me. This is the SQL query that
I'm trying to run, I'm obviously missing something because only the last
letter in the ResAddr field is being updated to the ResStreet field. Any help
will be greatly appreciated.


UPDATE [Edison Voter File] SET [Edison Voter File].ResStreet = Trim(Mid(
[ResAddr],Len([ResAddr]),Len([ResAddr])-Len([ResHse])-Len([ResType])))
WHERE ((([Edison Voter File].VoterID) Is Null));

What SQL query would actually take what is in between the first " " and the
last " "?

Long time reader first time poster.

 

[upstate-dem]

(Mid([ResStreet],InStrRev([ResStreet]," ")+1)) is what i've come to use and
will have to eye the problem children that occur

 

[John Vinson]

(Mid([ResStreet],InStrRev([ResStreet]," ")+1)) is what i've come to use and
will have to eye the problem children that occur

That's about your best bet... and there WILL be problems (I live a
very short walk from Old Fort Boise Road).

John W. Vinson[MVP]

 

[John Nurick]

If you find this leaves too many "problem children", there's an example
of using a regular expression to parse street addresses (actually in
greater detail than you want) at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm

(Mid([ResStreet],InStrRev([ResStreet]," ")+1)) is what i've come to use and
will have to eye the problem children that occur

I've been able to take the house # and Street type and put them into their
proper fields. The street name (being anything in between the first and last
" " of the full address) seems to be stumping me. This is the SQL query that
I'm trying to run, I'm obviously missing something because only the last
letter in the ResAddr field is being updated to the ResStreet field. Any help
will be greatly appreciated.

UPDATE [Edison Voter File] SET [Edison Voter File].ResStreet = Trim(Mid(
[ResAddr],Len([ResAddr]),Len([ResAddr])-Len([ResHse])-Len([ResType])))
WHERE ((([Edison Voter File].VoterID) Is Null));

What SQL query would actually take what is in between the first " " and the
last " "?

Long time reader first time poster.