Separating Field

[didacticone]

First off, sorry. Very new to access. I have a table that has street number and street name in the same field. Ex. "131 N Maple Ave". I would like to separate the number and put it in its own field and leave the remaining street name in the existing field. Ex. "131" in the new field, lets call it "streetno" and "N Maple Ave" kept in the existing field. Thank you

[missinglinq]

Parsing addresses into components, like parsing entire names, almost always turns into a hairball. Years ago I moonlighted doing data entry for a major banking system. They had bought out another bank whose customers' addresses were entered like yours are and wanted them converted in the manner you speak of. A team of three experienced developers spent 3 months trying to automate this and finally gave up, hence the re-entering of data for hundreds of thousands of customers! If all the addresses took the form

131 N Maple Ave

it would be fairly straightforward . Unfortunately, they seldom do! You can have

131 N Maple Ave

131-A N Maple Ave

131 N Maple Ave Apt 12

131 N Maple Ave Bldg A Apt 12

and so forth. In short, if you manage to get some of them parsed out successfully, you're still going to have to manually inspect all of them in order to catch the many that will fall between the cracks.

On the off chance that all your addresses have the format you've given:

StreetNumber = Val(Me.OriginalAddress)
StreetName = Right(Me.OriginalAddress, Len(Me.OriginalAddress) - InStr(Me.OriginalAddress, " "))

will do the job.

Welcome to Bytes!

Linq ;0)>

[didacticone]

i tried what you said and it is displayed in the post below...sorry for this post i hit the wrong reply button. thanks!

[didacticone]

Parsing addresses into components, like parsing entire names, almost always turns into a hairball. Years ago I moonlighted doing data entry for a major banking system. They had bought out another bank whose customers' addresses were entered like yours are and wanted them converted in the manner you speak of. A team of three experienced developers spent 3 months trying to automate this and finally gave up, hence the re-entering of data for hundreds of thousands of customers! If all the addresses took the form

131 N Maple Ave

it would be fairly straightforward . Unfortunately, they seldom do! You can have

131 N Maple Ave

131-A N Maple Ave

131 N Maple Ave Apt 12

131 N Maple Ave Bldg A Apt 12

and so forth. In short, if you manage to get some of them parsed out successfully, you're still going to have to manually inspect all of them in order to catch the many that will fall between the cracks.

On the off chance that all your addresses have the format you've given:

StreetNumber = Val(Me.OriginalAddress)
StreetName = Right(Me.OriginalAddress, Len(Me.OriginalAddress) - InStr(Me.OriginalAddress, " "))

will do the job.

Welcome to Bytes!

Linq ;0)>

Thank you for your help. Sorry again, but I entered this:

SELECT
FROM Meters;
StreetNumber = Val(Me.Address)
StreetName = Right(Me.Addres s, Len(Me.Address) - InStr(Me.Addres s, " "))

and I got this error message:

The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

I am so new to this. I'm pretty sure that there needs to be a WHERE and END statement somewhere, I just don't know. The table is called "Meters", and the field that has the addresses is called "Address". I know that i'm gonna have to manually go through the fields once it works, but luckily we only have like 3,000 entries.

Thank you.

[missinglinq]

Ther code I gave you was for use in VBA, not in a query. The syntax is slightly different in queries.

SELECT Meters.Address, Val([Address]) AS StreetNumer, Right([Address],Len([Address])-InStr([Address]," ")) AS StreetName
FROM Meters;

Linq ;0)>

[didacticone]

Ther code I gave you was for use in VBA, not in a query. The syntax is slightly different in queries.

SELECT Meters.Address, Val([Address]) AS StreetNumer, Right([Address],Len([Address])-InStr([Address]," ")) AS StreetName
FROM Meters;

Linq ;0)>

awesome...it worked great... now its just a matter of finding some of the unusual ones like you said... thanks a lot for your help!

[missinglinq]

Glad we could help!

Linq ;0)>