need to seperate field into first, last, middle initial

need to seperate field into first, last, middle initial

• shy_man

  If the field uses a unique character to delimit the different parts, use the Split function on the original string. For example:

  Split("Smith/John/A/Master of His Domain", "/")

  will give you an array containing:

  Smith
  John
  A
  Master of His Domain

  You can then do what you will just by accessing the array elements.

  
  

• Kikala

  Cathyjack wrote:

  I am using an access database but would like to write the code in vb within access. I need to keep info in original database

  This is what the example I posted addresses.

  
  

• Mats Backlund

  If you have them in VB, then you can seperate them using regular expressions.  If you want to do it within the database, you should ask in the SQL forums.

   <edit> Let me clarify: if you want to pull the data into VB and then store it again, you can use regular expressions.  If you want to write a stored proc, so the data gets manipulated entirely by SQL, then you need to ask in a SQL forum </edit>

  
  
  

• RatOmeter

  If you really want to do this in an access query its not going to be easy. but if you want some crude code here's something you can do:

  I'm assuming your table is called Table1
  I'm also assuming your field is called Field
  and in the field you have a text string "last first middle"
  So to seperate the last, first and middle names with a query I'd use:

  SELECT Trim(Left([field],InStr([field]," "))) AS Expr1, Trim(Mid([field],Len([expr1])+1,InStr(Len([expr1]),[field]," ",1)+1)) AS expr2, Mid([field],Len([expr1])+Len([expr2])+3,InStrRev([field]," ")) AS expr3
  FROM Table1

  Like I said it's not pretty but its gets the job done.

  This also means that you can use functions in queries in access...

  This worked in my test table... hope it works for you.

  
  

• Monarghel

  Can you post a few examples of your data Also, are all of the fields formatted the same way In other words, are all the elements of the field separated using the same character, either a space or a comma or something similar
  

• Judge Of Nights

  Cathyjack wrote:

  I have a field with last name, first name, middle initial, second first name, second MI, Title. I need to seperate these out to seperate columns. I have done it in 25 queries in access. Is there an easier way

  When Cathyjack said that it took 25 queries in access, I thought it meant was it possible with fewer queries... Thats why I tried the query solution.

  
  

• Nirvana

  The OP said she wants to do this in Access code, not SQL.

  Here's a quick-and-dirty example of what I was describing:

  Option Explicit
  Option Compare Database

  Sub WriteSplitFieldsTest()

      Dim rsInput As New ADODB.Recordset
      Dim rsOutput As New ADODB.Recordset
      Dim OldName() As String

      rsInput.Open "TableWithComplexField", CurrentProject.AccessConnection, adOpenStatic, adLockReadOnly
      rsOutput.Open "TableWithSplitFields", CurrentProject.AccessConnection, adOpenStatic, adLockBatchOptimistic

      With rsInput
          Do Until .EOF
             Erase OldName
             ' assumes a slash character is the field delimiter
             ' and all fields are present in the source field
             OldName = Split(.Fields("the_complex_field") & "", "/")
             rsOutput.AddNew Array("firstname", "middlename", "lastname"), Array(OldName(0), OldName(1), OldName(2))
             .MoveNext
          Loop
      End With
     
      rsOutput.UpdateBatch
      rsOutput.Close
      rsInput.Close

  End Sub
  

  
  

• DJSQ

  I am using an access database but would like to write the code in vb within access. I need to keep info in original database
  

• Kiran Suthar

  Does the field seperate the values with a comma or something unique

  If it does, you can use the string functions within VBA (Left, Mid, Right, Instr etc) to parse the individual fields and then write them out to a new table.

  
  

• zobrakster

  OK - I have no experience with VBA, but if you look for 'regular expressions' within your help, if VBA supports them ( and I think it does ), then that's almost certainly the way to go.

  
  
  
• need to seperate field into first, last, middle initial