Splitting a string into smaller bits

[terre]

Is there a query that could split the following....which all appear in one field

A Baby Sister for Frances Hoban, L. 24 2.7
A Clean House for Mole and Mouse Ziefert, H. 14 1.7
A Color of his Own Lionni, Leo 17 2.0

into a table with 4 fields like below?


Title Author RR Level
A Baby Sister for Frances Hoban, L. 24 2.7
A Clean House for Mole Ziefert, H. 14 1.7
A Color of his Own Lionni, Leo 17 2.0

 

[MajP]

SELECT getTitle([strTitle]) AS Title, getLastName([strTitle]) AS LastName, getFirstName([strTitle]) AS FirstName, getRR([strTitle]) AS RR, getLevel([strTitle]) AS [Level]
FROM tblWhere;

Public Function getLastName(strInfo As String) As String
  Dim I As Integer
  I = InStr(strInfo, ",")
  Do While Not Mid(strInfo, I - 1, 1) = " "
    getLastName = Mid(strInfo, I - 1, 1) & getLastName
    I = I - 1
  Loop
End Function
Public Function getFirstName(strInfo As String) As String
  Dim I As Integer
  I = InStr(strInfo, ",") + 2
  Do While Not Mid(strInfo, I, 1) = " "
    getFirstName = getFirstName & Mid(strInfo, I, 1)
    I = I + 1
  Loop
End Function
Public Function getLevel(strInfo As String) As String
  Dim I As Integer
  I = InStrRev(strInfo, " ")
  getLevel = Mid(strInfo, I)
End Function

Public Function getRR(strInfo As String) As String
  Dim I As Integer
  I = InStrRev(strInfo, " ")
  Do While Not Mid(strInfo, I - 1, 1) = " "
    getRR = Mid(strInfo, I - 1, 1) & getRR
    I = I - 1
  Loop
End Function

Public Function getTitle(strInfo As String) As String
  Dim I As Integer
  I = InStr(strInfo, ",")
  getTitle = Left(strInfo, I - 1 - Len(getLastName(strInfo)))
End Function

This works 100% for the examples provided, but it will probably work about 70 % of the time with other cases. There is no real rules to apply. I keyed off of the comma as the thing that separated the first and last name. But if you have one or more commas in the title or a name with a space "example De Leon" then it will not work.

 

[terre]

Thank you.
It gives me something to work with

 

[MajP]

Since your data looks like this:
A Baby Sister for Frances Hoban, L. 24 2.7
A Baby Sister for Frances, Named Baby Susie Hoban, L. 24 2.7

The last comma divides the last name from the middle initial.
So if the Title has a comma in it, look for the last comma. Currently I was searching for the first comma. Therefore modify my functions "getlastname" and "getFirstName" using an "instrrev" instead of an "instr". That will find the last comma. Ex