Parsing an Access 97 Field

[tbohon]

One of the fields in my current project database is in Last, First MI order (all in the same field.)&nbsp;&nbsp;Field is a simple text field (actually imported from an Excel 97 ss.)<br><br>I need to break it apart into three separate fields (last name, first name and MI).&nbsp;&nbsp;Also, some names don't have initials.<br><br>I can export it to delimited text and write a quick c program to do this - but was wondering if there's a way to do it within Access 97, i.e., an update query, or a button on a one-time-use form or ... ???<br><br>Thanks in advance for the help!<br><br>Tom

 

[DougP]

in VB its like so<br>&nbsp;&nbsp;&nbsp;&nbsp;' But this does not take in to account no middle initial<br>&nbsp;&nbsp;&nbsp;&nbsp;' OR other unusual names like &quot;Dr. Smith, Frank D.&quot;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim wholeName As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim last, first, middle As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim FindComma, FindSpace As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;wholeName = &quot;Smith, Sam B.&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;FindComma = InStr(1, wholeName, &quot;,&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;last = Left(wholeName, FindComma - 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;FindSpace = InStr(FindComma + 3, wholeName, &quot; &quot<br>&nbsp;&nbsp;&nbsp;&nbsp;middle = Right(wholeName, Len(wholeName) - FindSpace)<br>&nbsp;&nbsp;&nbsp;&nbsp;first = Mid(wholeName, FindComma + 2, Len(wholeName) - Len(middle) - Len(last) - 3) <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Cornerstone]

Create the fields in the table then Open an Access Module and write the following:<br><br>dim dbs as database<br>dim rst as recordset<br><br>set dbs=currentdb<br>set rst=dbs.openrecordset(&quot;yourtablename&quot<br><br>rst.movefirst<br><br>do until rst.eof<br>rst.edit<br>rst!lastname=left(rst!fullname,instr(rst!fullname,&quot;,&quot-1)<br>rst!firstname=mid(rst!fullname,instr(rst!fullname,&quot;,&quot+1)<br>rst.update<br>rst.movenext<br>loop<br><br>If you have lots of records with initials then add a routine to loop through the first name field and do the same thing to put the initial in a middle initial column. There are other ways too.

 

[Amiel]

Yes you can parce the field using VBA (or VB), pretty much without limitation. You end up using the VBA functions:<br><b>InStr()</b>&nbsp;&nbsp;equvilent to C's <i>strstr</i> and <b>Mid()</b> which allows you to extract portions of the string by including a starting offset, and the number of characters to extract.<br><br>The logic for the types of comparisons that will have to be performed will be the same in both languages. The coding that is required to perform the particular comparison will be much different of course. Im sure that you are aware of much of this, but please read on...<br><br>Of course using 'C' the module you would have to (1) open the database, (2) open a recordset, (3) open the destination text file, (4) loop through the record set performing data type conversions for variants prior to string comparisons, (5) do comparisons and parce the string(s) in question,(6) create 'records' by adding delimiters between fields for each record,(7) write the formatted string for each record to a text file,(8) then close the text file, recordset and database. <br><br>This would have to be compiled of course. Meaning that the paths to the database and the destination text file will be hard coded unless you allow them to entered on the command line. Add a dozen more lines of code for that.<br><br>Then, once compiled, if the logic is found to be inadequate and require changes you'll have to modify the source code and recompile. Only after all of the above is functioning correctly can you then <i>import the text</i> into an Access table! Whew.<br><br>In Access the entire routine looks like this:<br>Assuming that a record should look like this<br><br>&quot;lastname, firstname mi.&quot;<br><br>Function DoParceFieldRoutine()<br>Dim db as database<br>Dim SourceRS as RecordSet<br>Dim DestinationRS as RecordSet<br>Dim strBuff1 as string<br>Dim pos1 as Long<br>Dim pos2 as Long<br><br><br>set db = DBEngine(0)(0)<br>set SourceRS = db.openRecordSet([SourceTable])<br>set DestinationRS = db.openRecordSet([DestinationTable])<br><br>do while not&nbsp;&nbsp;SourceRS.EOF<br>' ... <br>' ... <br>' ... <br>' ... <br>' Code to parce field in source table<br><br>&nbsp;&nbsp;strBuff1= SourceRS.Field(&quot;FieldInQuestion&quot<br>&nbsp;&nbsp;pos1 = InStr(strBuff1,&quot;,&quot 'is there a comma ?<br><br>&nbsp;&nbsp;If pos1 = -1&nbsp;&nbsp;then<br>&nbsp;&nbsp;&nbsp;' Do something with record lacking a comma<br>&nbsp;&nbsp;&nbsp;' meaning &quot;first, last&quot; doesnt exist or is incorrect<br><br>&nbsp;&nbsp;else <br>&nbsp;&nbsp;&nbsp;&nbsp;'is there a space&nbsp;&nbsp;after the&nbsp;&nbsp;first, last portion ?<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pos2 = InStr(pos1 + 1, strBuff1,&quot; &quot <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if pos2 = -1 then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Do something w/ record lacking a lastname (space)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'otherwise extract each portion using the<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' mid()function and add it to the <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'destination table. <br>&nbsp;&nbsp;DestinationRS.AddNew<br>&nbsp;&nbsp;DestinationRS.Fields(&quot;firstname&quot = mid(strBuff,1,pos1)<br>&nbsp;&nbsp;DestinationRS.Fields(&quot;lastname&quot&nbsp;&nbsp;= mid(strBuff,pos1,pos2)<br>&nbsp;&nbsp;DestinationRS.Fields(&quot;mi&quot = mid(strBuff,pos2)<br>&nbsp;&nbsp;DestinationRS.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;End IF<br>&nbsp;SourceRS.MoveNext<br>Loop<br>End Function<br><br><br>When we reach this point the job is done. You'll have to trace throught the code to get the 'pos' variables used in the mid function correct. <b><u>I'm sure the above is not perfect,</u></b>&nbsp;&nbsp;but its close. It seems a better (certainly less time consuming) solution than creating this same routine in 'C'.<br><br><br>Enjoy !<br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[jimmythegeek]

I know I am late on this, but the following function works great, I have used it many times. You can receive the name spelled nine different ways, and this will parse it.<br><br>Just copy the whole thing into a new module, replace the bolded area's, open the immediate window and type &quot;Call ParseName&quot;<br><br>Hope this helps<br><br><br><br>Function ParseName()<br>&nbsp;&nbsp;&nbsp;&nbsp;'This function breaks apart a single name field into seperate first, last&nbsp;&nbsp;& middle initial fields.<br>&nbsp;&nbsp;&nbsp;&nbsp;'This will work for a name entered in any of the following ways:<br>&nbsp;&nbsp;&nbsp;&nbsp;' {Smith, John}&nbsp;&nbsp;{Smith, John D}&nbsp;&nbsp;{Smith, John D.}<br>&nbsp;&nbsp;&nbsp;&nbsp;' {Smith,John}&nbsp;&nbsp;&nbsp;{Smith,John D}&nbsp;&nbsp;&nbsp;{Smith,John D.}<br>&nbsp;&nbsp;&nbsp;&nbsp;' {Smith John}&nbsp;&nbsp;&nbsp;{Smith John D}&nbsp;&nbsp;&nbsp;{Smith John D.}<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'Start by adding 3 fields to the table where your single name field is (firstname, lastname, & MI)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim fldName As Field<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim x As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strLast As String, strFirst As String, strMI As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(&quot;<b>TableNameHere</b>&quot;, dbOpenDynaset)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'open appropriate table<br>&nbsp;&nbsp;&nbsp;&nbsp;Set fldName = rs![<b>NameFieldHere</b>]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'single name field<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until rs.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If IsNull(rs!FirstName) Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = InStr(1, fldName, &quot;,&quot&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If x = 0 Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = InStr(1, fldName, &quot; &quot&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strLast = Left(fldName, x - 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Mid(fldName, x + 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Right(strFirst, 1) = &quot;.&quot; Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 3)&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = strFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(fldName, x + 1, 1) = Chr(32) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strLast = Left(fldName, x - 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Mid(fldName, x + 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Right(strFirst, 1) = &quot;.&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 3)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = strFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strLast = Left(fldName, x - 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Mid(fldName, x + 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Right(strFirst, 1) = &quot;.&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 3)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = Right(strFirst, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = Left(strFirst, Len(strFirst) - 2)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strMI = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strFirst = strFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;!LastName = strLast&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;!FirstName = strFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;!MI = strMI<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;db.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Function<br>