VBA routine that would organise my messy table

Hi there

I am little new in programming with VBA so I need some help.

I have at table that holds one column. In that column there are several pieces of information that needs to be put into their own column. Here is an example

ID Name_column
1_______Åhman John (EFG-878)
2_______Von Gerdten Maria (ERT-234)
3_______
4_______Linjos-Contrino Armando (NY-987)
5_______Nordberg Bo-Christian
6_______Rico-Ruiz M
7_______ART-675
8_______Hedlund
etc…

I think that one word names would simple to separate. Also the names with hyphen would be possible in some degree to separate but names like Von Gerdten Maria will be difficult to detect by code. I could do this manually as there are not so many such occasions.
Sometimes there is no information to put to a certain column. But still I would like to have a table that looks like this. I will fill the missing information later.


ID______surname_______first_name____registernumber
1_______Åhman_________John________EFG-878
2_______Von Gerdten____Maria________ERT-234
3_______
4_______Linjos-Contrino _Armando______NY-987
5_______Nordberg_______Bo-Christian
6_______Rico-Ruiz_______M
7__________________________________ART-675
8_______Hedlund

I’m using access 2002
I would appreciate all the help for my problem.

Ismo

[nunca_habla]

You're searching for the last occurence of a space in a string so you use something like this code:

dim db as database
dim rs as recordset
dim a as long
dim s as string
dim position as long

set db=currentdb
set rs=db.open("MyTable")
' --- insert here the standard check if rs is opened ---
...
' ---
rs.Movelast
rs.movefirst
for a=1 to rs.recordcount
s= rs!YourField
rs.edit
position=instrrev(s," ")
rs!surname=left$(s,position-1)
rs!first_name=right$(s,len(s)-position)
rs.update
rs.movenext
next
rs.close
'  etc... close all

I hope I didn't mistype anything in code because i did this without VB-Editor

[Bat17]

I have made some assumptions.
Reg Number will be at least two capital letters followed by a hypen followed to at least two numbers

There will be no first Name with out a surname.

* will not be used in your text, if it is you will need to change the symbol in
inputstr = Replace(inputstr, " ", "*", 1, 1)
aStrOut(0) = Replace(aStrIn(0), "*", " ")
to one that won't be used.

Public aStrOut(3) As String

Function FunGetNames(inputstr As String) As Variant
Dim re, aStrIn() As String
' clean up string
inputstr = Replace(inputstr, "(", "")
inputstr = Replace(inputstr, ")", "")
inputstr = Trim(inputstr)

' find if there is a reference and add to aStrOut and remove from name string
aStrIn = Split(inputstr, " ")
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[A-Z][A-Z]+\-[0-9][0-9]+"
re.IgnoreCase = False
If re.test(aStrIn(UBound(aStrIn))) Then
    aStrOut(2) = aStrIn(UBound(aStrIn))
    inputstr = Trim(Replace(inputstr, aStrIn(UBound(aStrIn)), ""))
Else
    aStrOut(2) = ""
End If
Set re = Nothing
' check for names
If Len(inputstr) = 0 Then ' no names found
    aStrOut(0) = ""
    aStrOut(1) = ""
Else
    aStrIn = Split(inputstr, " ")
    Select Case aStrIn(0) ' check for pre-fixes
        Case Is = "von", "xyz", "ABC" ' set up prefixes here
            inputstr = Replace(inputstr, " ", "*", 1, 1)
    End Select
    aStrIn = Split(inputstr, " ")
    Select Case UBound(aStrIn) + 1
    Case 1 ' last name only
        aStrOut(0) = Replace(aStrIn(0), "*", " ")
        aStrOut(1) = ""
    Case 2 ' both names
        aStrOut(0) = Replace(aStrIn(0), "*", " ")
        aStrOut(1) = aStrIn(1)
    Case Else ' give warning and show problem text
        MsgBox "Too many names in " & inputstr, vbCritical, "Error"
        aStrOut(0) = inputstr
        aStrOut(1) = ""
    End Select
End If
End Function

Sub test()
Call FunGetNames("Rico-Ruiz M")
Debug.Print aStrOut(0)
Debug.Print aStrOut(1)
Debug.Print aStrOut(2)
End Sub

HTH

Peter

Edited to add info about *

Tank you very much about these routines Peter and nunca hable. This has help me.

My intention is to use access table as a source of information and also have the output in new table.

How do I read my column where the names and register numbers are to a recordset? And after the recordset how I can change the recordset to this aStrIn() array.

After the separated strings are in aStrIn() variable array how I can change back to a recordset so that I can insert these strings to their proper columns in a access table.

I will greatly appreciate further information.

Ismo