Middle Initials 1

[SoClueless]

Hi all...

Has anyone imported a list where middle initials were in the same field with first name? Is there a relatively easy way to pull them out and put them into my middle initial field without having to fix them individually?

Thank you.

 

[Elysium]

You can use the RIGHT function that will grab the middle initial. This is relatively easy provided that the field doesn't have the last name as well. If it does, then there are a few more steps. Ex:

sName = "Johnny Q"

sMName = Right(sName, 1)
sFName = Left(sName, Len(sName) - 2)

sMName will give you the middle initiatl. sFName will give you the first name only. Now in order to fix this in the table, you need to add a middle initial field, create a procedure to loop through the table, and while looping, update the first name field and add to the middle name field. You could also do this with an update query if you wanted to.

RS

 

[SoClueless]

Hi RS,

Thanks so much for the tip. I'm having a little trouble with this, as I don't do this often enough. When I put that expression in a field, I get a parameter query. I'm struggling to figure out. (or i'll be fixing 2500 records one by one, ouch)... thank you again.

PS

 

[Elysium]

Let's say that your table structure is such:

fldFirstName <--- has the first name and middle initial
fldLastName

Do the following:

01) Go into Design view of your table and add a third column called fldMiddleIni.

02) Create an update query with the following SQL:

UPDATE tblNames SET tblNames.fldFirstName = Left([fldFirstName],Len([fldFirstName])-2), tblNames.fldMiddleIni = Right([fldFirstName],1);


This will update and correct every record in your table. Now, if this is still unclear, give me your table structure (include all field names and the table name) and I will give a more pointed example. But, you shouldn't have a problem with this.

RS

 

[SoClueless]

Hi again,

Moving right along, this was very good and thank you. Now I seem to have a problem b/c I need, i think, a WHERE statement so that this update only happens to the records which actually have the middle initials in the FirstName field. I used WHERE (((tbltestOct28.[First Name]) LIKE &quot;*.&quot) with no results. All initials are followed by period. I tried the WHERE statement both before and after the UPDATE. Any more ideas? Thanks for your patience.

PS

 

[Elysium]

Okay, for this answer I should deserve a star . You have to do a little code for this, so here goes.

This SQL below builds on what I gave you yesterday. The criteria looks for any spaces in the first name. If there is a space, then only that record will have the middle initial pulled out. It also takes into account a first name like &quot;Billy Bob&quot; and disregards it because &quot;Bob&quot; is really part of the first name. Let me know how it goes.

UPDATE tblNames SET tblNames.fldFirstName = Left([fldFirstName],Len([fldFirstName])-2), tblNames.fldMiddleIni = Right([fldFirstName],1)
WHERE ((InStr(1,[fldFirstName],&quot; &quot>&quot;0&quot; And Mid(Right([fldFirstName],2),1,1)=&quot;&quot);


RS

 

[Elysium]

I just realized that you said all initials are followed by a period. In that case, you will have to adjust the query and make it like this:

UPDATE tblNames SET tblNames.fldFirstName = Left([fldFirstName],Len([fldFirstName])-2), tblNames.fldMiddleIni = Right([fldFirstName],1)
WHERE (((InStr(1,[fldFirstName],&quot; &quot)>&quot;0&quot AND ((Mid(Right([fldFirstName],3),1,1))=&quot;&quot);


I just changed the 2 to a 3 in the WHERE statement. Now it works!!

RS

 

[SoClueless]

Thank you RS and here is a large Gold Star!!!!! Slight modification Right([fldFirstName],2 instead of 1 in order to get the initial along with the period and you've set me up beautifully....test table successful, now I'm off to apply it to the real thing. Have a great weekend and thanx again!!!!!!!

PS

 

[Elysium]

I was talking about the pink star you see marked with &quot;Mark this post as a helpful/expert post!&quot;. Gold is nice, but pink is better in this case.

RS

 

[SoClueless]

oops, Pink Star it is, then!