splitting up a field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
-----Original Message-----
I have a table with an address formatted as "1234 some street name"

I want to add three colums to the table for stnum,
Click to expand...
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
 
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?
 
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.
 
It also generated an interesting side effect of turning "113 E 130th street" into 1.13E+13

this could be a problem. is there a way to only have it capture everything up to the first space?
 
Ok. I took care of that nasty exponent problem by changing it to

StrNum: Val(Left([ResAddress1],InStr(4,[ResAddress1]," ",1)))
 
Thanks for the help. These are the final statements I used

StrNum: Val(Left([ResAddress1],InStr(1,[ResAddress1]," ",1))
Parity: Val(Left([ResAddress1],InStr(1,[ResAddress1]," ",1))) Mod
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.
 
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.
Click to expand...

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).
 
Back
Top