vba - How to split a Name Field that has incorrect data? -

i have table field called patrn_name set first_name, last_name m.i.

examples:

smith, james m

jones, chris j.

i trying break field first_name, last_name , mi fields. asked question , helped me use split() last_name field. however, when try use split() function first_name not work because field has records not follow name convention of field , instead follows: "town library - gw" or "donation new york city".

when code encounters these types of names throws error "subscript out of range" on line using rst!first_name = split(trim(split(rst!patrn_name, ",")(1)), " ")(0). how can make code run on data follows standard name convention of field?

function change_name()  dim dbs dao.database dim rst dao.recordset  set dbs = currentdb set rst = dbs.openrecordset("active patrons", dbopendynaset)  rst.movefirst  while not rst.eof      rst.edit     rst!last_name = split(rst!patrn_name, ",")(0)     rst!first_name = split(trim(split(rst!patrn_name, ",")(1)), " ")(0)     rst.update     rst.movenext  loop end function 

you have 2 splits: once comma; space. declare 2 string arrays hold results splits.

dim astrcomma() string dim astrspace() string 

then think simpler using arrays in loop.

rst.edit astrcomma = split(rst!patrn_name, ",") if ubound(astrcomma) > 0     ' means patrn_name contains @ least 1 comma,     ' assume last_name before first comma     rst!last_name = astrcomma(0)     ' expect first_name present in second member of astrcomma     astrspace = split(trim(astrcomma(1)), " ") else     msgbox "no last_name in " & rst!patrn_name end if  if ubound(astrspace) >= 0     ' may want check whether empty     ' string before store it; field allow      ' empty strings?     rst!first_name = astrspace(0) else     msgbox "no first_name  in " & rst!patrn_name end if rst.update