Access Help Seperating

[mrmoo2525]

My problem is that i have an adress field Ex: 1409 State St. i need the 1409 and the State St to be seperate. Im new to Acess and was more or less given the database and told to use it. not really sure where to go. Thanks a bunch if anyone can help me.

[Stwange]

My problem is that i have an adress field Ex: 1409 State St. i need the 1409 and the State St to be seperate. Im new to Acess and was more or less given the database and told to use it. not really sure where to go. Thanks a bunch if anyone can help me.

Don't double post again.

Private Function separateAddress(ByVal address As String)
    Dim i As Integer
    i = 1
    Do While IsNumeric(Left$(address, i)) And Mid$(address, i, 1) <> ""
        i = i + 1
    Loop
    i = i - 1
    If i <> 0 Then MsgBox "Number: " & Mid$(address, 1, i - 1) & ", Street: " & Mid$(address, i + 1)
End Function

[missinglinq]

And if the address is, for instance, 221 B Baker Street? Or even worse,
221B Baker Street!

Welcome to TheScripts, mrmoo2525!

Linq ;0)>

[Stwange]

And if the address is, for instance, 221 B Baker Street? Or even worse,
221B Baker Street!

Point taken, in that case maybe parsing for the first string would be better than looking for numeric. The first example is worse though.

If he could provide a parse rule for his system, eg.
The address will be either the second or third "word" onwards, but must start with at least two letters, eg. 12 B The Ferns or 12B The Ferns, but never 12 BA The Ferns?

[missinglinq]

Parsing addresses is always a hairball! No matter how many rules you set up, something will always come along that doesn't fit into any of the pidgeon-holes you've designed! When it comes to addresses the possibilities are endless! So you set up the rules like you suggested"

The address will be either the second or third "word" onwards, but must start with at least two letters, eg. 12 B The Ferns or 12B The Ferns, but never 12 BA The Ferns?

Then what happens if instead of 221 B Baker Street it's 221 Apt B Baker Street? In the end you do, indeed, have to decide on a set of rules, and after you've done the automatted conversion, go back in and manually inspect each address.

[mrmoo2525]

I thank ALL of you who responded to my thread.
My next question is how I execute the thread. Via SQL Query? Very new to access and more specific instructions would be much appreciated!
Thanks.