splitting up a field?

  • Thread starter John W. Vinson [MVP]
  • Start date
J

John W. Vinson [MVP]

-----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
 
G

gsiebe

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?
 
G

gsiebe

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.
 
G

gsiebe

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?
 
G

gsiebe

Ok. I took care of that nasty exponent problem by changing it to

StrNum: Val(Left([ResAddress1],InStr(4,[ResAddress1]," ",1)))
 
G

gsiebe

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