Question about Stripping a Field

[exm206]

I have an address field in a table of about 200,000 records. The format for the field is the house number, then the street name. These addresses were entered free hand so there is great variation in how they are entered. For example, some may say "100 Main St." or " 100 Main Street" or " 100 Main St". As you can see, there may be a number of spaces before the house number. Another problem is that the house number can be any number of digits. The one constant is that every record has only one space between the house number and the street name. What I am trying to do is create a new field with just the street name. For example, if the address is "100 Main Street", I want the new field to say "Main Street". I have tried the Left, Right, and Mid functions, but there are so many variations on the length of the house number and the length of the field as a whole that I would have to 30 or 40 queries to get them all. My question is, is there a way to strip just the Street name from this field. In other words is there some way I can tell Access to give me everything after the space between the house number and the street name? Any help you could give me would be greatly appreciated.

 

[Russell Hauf]

You can use the Val function for this. Try something like:

Number: Val([Addr])

Street: Mid([Addr],Len(CStr(Val([Addr])))+2)

where Addr is your address field.

HTH,

Russell

 

[Russell Hauf]

Note that this will not work in cases where something like "0012 Main Street " was put in for an address...but hopefully you don't have anything like that.