Remove or Seperate Address Field

[georget]

I have an Address Field I want to seperate into two fields. So I created two more fields and populated it with the Address field.

1) StreetNo
2) StreetName

Then I did an Update Query to remove the street numbers. I used the following in the Update to:

Right([streetname],Len([streetname])-InStr([streetname]," "))

It almost worked, but it removed the first name of the street when there was no street number. I searched in Access Help and on the MS site, no luck.

Any ideas? Or a better process?

Thanks!

 

[Jon K]

In the streetname column set the Criteria:
Like "[0-9]*"

which means streetname beginning with a digit of 0-9.

 

[raskew]

Due to the diversity of street addresses, what you're proposing
is a losing battle. Try applying some sort of logic to an address
which looks like: 123 Tin Pan Alley SE, Apt. 32.

As a general rule, routines that attempt to separate the elements
of an address divide them up as:

Full street address:
City
State
Zip Code

With luck, commas will separate Street Address / City and
City / State, e.g.

123 Tin Pan Alley SE, Apt. 32, New Orleans, LA 70113

With this, you could work right to left and, with some deft coding,
properly identify the various components.

 

[gblack]

You can do it but. . . .

OK here’s how you do it:

Make a Make Table query and create a special field (call it StNumber. . .or whatever you want). The add some code as I have below:

So. . .

StNumber: IIf(Int(Left(trim([Address]),2))>0,Left([Address],InStr([address]," ")-1),"Doesn't matter")

When you view this query all the street numbers will appear but the places where there is text will give you an error. . .this is fine. You will also need PK field of some sort.

When you run the Make table query, it will give you a table of all the numbers, but will put in null values in the areas where there are none.

Once you have this you can probably think of how to get the rest. . .I would take that table link it back up to the address table via the PK and then make a new make table query with a both the fields you want. You have the first field already StNumber and the second field is easy just do

Street Name: right(address, len(address)-len(StNumber)). . .or something close to that. . . I forget if the length of a null field is zero or not. . .if it isn’t you’ll have to use an iif to do something with the null values in StNumber

It would be a problem if the NUmber is like a234 whatever street, but other than that you should be OK. . .

The reason I use Left(address,2) is for things like 1st street and such. . .

 

[AccessUser21]

I had the best luck with gblack's post. Below is the code I used, worked like a charm!


StNumber: IIf(Int(IIf(IsNumeric(Left(Trim([Address]),2))=True,Left(Trim([Address]),2),0))>0,NZ(Left([Address],InStr([address]," ")-1),0),"Doesn't matter")

AddressNames: IIf([StNumber]="Doesn't matter",[address],Right([address],Len([address])-(Len([StNumber])+1)))