Seperating an address into two new fields 1

[Rslender]

I'm trying to convert a table with customer data for use with another system but I'm running into a problem.
In the current table there is only one field for both the streetname and number. In the new database these need to be seperated.
The problem I'm facing is that the format of the address is not consistent.

Here are some examples:

Postbus 1
De Stoevelaar 100
Burg. Jansenstraat 12a

What I want to do is to put the house number into one field and the streetname into another. I basically need to write a query which selects everything after the last space to get the housenumber but I've been unable to get the query right.
Does anyone know how I can solve this problem?

Thanks in advance,

Ronald

 

[scriverb]

Create a Public function in a database module as below:

Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim I As Integer
For I = vStart To 1 Step -1
If InStr(I, vSearchStr, vTargetStr, 1) = I Then
InStrRight = I 'Position of vTargetStr
Exit For
End If
Next I
End Function

Create a query and paste in this SQL:
Select A.FullAddress, Mid$(A.FullAddress, 1, InStrRight(A.FullAddress, " ", Len(A.FullAddress)) - 1) as AddressPart, Mid$(A.FullAddress, InStrRight(A.FullAddress, " ", Len(A.FullAddress)) + 1) as NumberPart
FROM tblYourTable AS A;

You will have to update the query with the correct name of your table as well as the name of the FullAddress field. This function created searches a string for the postion of another sring from the right and returns that position Long Integer. So, basicially is searches the FullAddress field from the right looking for a " "(space) and the Mid$ function uses that position to parse your data field.

Let me know if you need any further assistance.



Bob Scriver

 

[Rslender]

It took me a while to get it working because I'm not that experienced with VBA code and put the public function code in the wrong place. I definitely need to learn more about that.
Your solution works perfectly and it saved me a lot of work because I was almost going to manually edit everything.
Thanks a lot for your help!

 

[scriverb]

Good luck. Glad to be of assistance. Get back with me if you have any further problems.

Bob Scriver