Splitting text strings at spaces

[mikemclean]

I have a field in an access table that carries the value for "Full Name". The format of this field is standard, being
Surname Initials Title

I would like to split this column into 3 different fields, namely Surname; Initials; Title, but cannot think of a short concise means of interogating the text string and finding the space character and creating this to produce my three datat fields.

Could anyone assist?

 

[lupins46]

Look at the Split() function in Help.

 

[VBslammer]

Create a function in a standard module so you can extract the data from anywhere in your project:

Option Explicit

[green]'@---------------------- Enumerated Type ----------------@[/green]

Public Enum NameTypes
  FirstName = 0
  MiddleName = 1
  LastName = 2
End Enum

[green]'@---------------------- Public Function ----------------@[/green]

Public Function GetNameAtPosition(ByVal strField As String, ByVal nameType As NameTypes) As String
  Dim names(0 To 2) As String
  Dim intNames As Integer
  
  intNames = GetNames(strField, names(0), names(1), names(2))
  GetNameAtPosition = names(nameType)
End Function

[green]'@--------------------- Private Function ----------------@[/green]

Private Function GetNames(ByVal FieldVal As String, _
                          ByRef FirstName As String, _
                          ByRef MiddleName As String, _
                          ByRef LastName As String) As Integer
  Dim strNames() As String
  
  strNames = Split(FieldVal, " ")
  
  Select Case UBound(strNames)
    Case 2
      FirstName = strNames(0)
      MiddleName = strNames(1)
      LastName = strNames(2)
      GetNames = 3
    Case 1
      FirstName = strNames(0)
      LastName = strNames(1)
      GetNames = 2
    Case 0
      FirstName = strNames(0)
      GetNames = 1
    Case Else
      GetNames = 0
  End Select
End Function

Call at runtime like this:
[tt]
fname = GetNameAtPosition(rst("FullName"), FirstName)
mname = GetNameAtPosition(rst("FullName"), MiddleName)
lname = GetNameAtPosition(rst("FullName"), LastName)
[/tt]
My experience is that fields that contain more than 1 word always contain abnormal entries, ie "Mr. Joe Smith" vs. "Joe R. Smith" vs. "Joe Smith Jr." so it may be difficult to use a single algorithm to split all the names properly.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[atlanticdit]

I have a similar issue with an over 2700 record Access database. I've put the code described above in the database's VBA module and I've also tried creating a program using VB6 to get it to work. Neither one has worked. I think the major issue, besides just my small base of knowledge, is that I'm not really sure where to put the "Call at runtime" portion of it. I keep getting errors that fname, mname, or lname is an invalid function or procedure. I've tried it without the fname, mname, or lname in it and am still getting errors or nothing changes with the data.

Any help would be appreciated as we're implementing a CRM package here and the data I'm working on needs to be ready in the next week or so.

Thanks,
Dave