|
|
|
splitting up a field? |
|---|
| message from =?Utf-8?B?Z3NpZWJl?= on 7 May 2004 |
I have a table with an address formatted as "1234 some street name"
I want to add three colums to the table for stnum, stname, and even/odd.
|
| John Vinson replied to =?Utf-8?B?Z3NpZWJl?= on 12 May 2004 |
Strnum: Val([address])
Stname: Mid([address], InStr([address], " ") + 1)
Oddness: Val([address]) MOD 2
Oddness will be 0 for even, 1 for odd.
This logic will fail for addresses like "The Beeches" or
"1812 1/2 Maple Avenue" (which will have street names of "Beeches" or
"1/2 Maple Avenue" respectively).
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| John W. Vinson [MVP] replied to =?Utf-8?B?Z3NpZWJl?= on 7 May 2004 |
street name"
stname, and even/odd.
You can get these with a Query - it probably would not be
either wise or necessary to store these values redundantly
in a table.
Create a Query based on your table; in two vacant Field
cells type
StrNum: Val([address])
and
Parity: Val([address]) MOD 2
The former will be the numeric address (note that
addresses like 1812 1/2 Elm St. and "The Willows" will
have discrepancies); the latter will be 0 for even, 1 for
odd.
If necessary, you can use the same expressions in an
Update query to store the fields permanently (and
redundantly!)
John W. Vinson/MVP
|
| =?Utf-8?B?Z3NpZWJl?= replied to John W. Vinson [MVP] on 7 May 2004 |
I guess what I really need to do is just take everything that is including and following the first letter in the field since there are a few that don't have street numbers.
|
| =?Utf-8?B?Z3NpZWJl?= replied to John W. Vinson [MVP] on 7 May 2004 |
StrNum: Val(Left([ResAddress1],InStr(1,[ResAddress1]," ",1)))
Parity: Val(Left([ResAddress1],InStr(1,[ResAddress1]," ",1))) Mod 2
StName: Trim(Mid([ResAddress1],InStr(1,[ResAddress1]," ",1),(Len([ResAddress1])-InStr(1,[ResAddress1]," ",1)+1)))
There may be a more elegant way to do some of this, but atleast it's working.
|
| =?Utf-8?B?Z3NpZWJl?= replied to John W. Vinson [MVP] on 7 May 2004 |
Thanks. That took care of the street number, and the even/odd thing. :)
Is there an easy way to make a string with the rest of the street name?
|
| =?Utf-8?B?Z3NpZWJl?= replied to John W. Vinson [MVP] on 7 May 2004 |
It also generated an interesting side effect of turning "113 E 130th street" into 1.13E+132
this could be a problem. is there a way to only have it capture everything up to the first space?
|
| =?Utf-8?B?Z3NpZWJl?= replied to John W. Vinson [MVP] on 7 May 2004 |
StrNum: Val(Left([ResAddress1],InStr(4,[ResAddress1]," ",1)))
|
|
