Moving some characters within one cell to another cell

[badger8]

I have a column which is the first name of contacts, but some of the names include their middle initial. I would like to move the middle initial characters to the middle initial column. There is alway a space between the first name and the middle name. How can I globally do this?

Thanks

 

[THoey]

Depending on what version you are using, there is a SPLIT command in A2K and in A97, you could use the INSTR command. SPLIT will create an array of separate fields based on the initial string and INSTR will tell you the location of the space between the names. I am used to A97, so, something like the following would work:

UPDATE MyTable (MyTable.FirstName, MyTable.MiddleName)
VALUES (LEFT(MyTable.FirstName, 1, (INSTR(MyTable.FirstName, ' ', 1) - 1)),
MID(MyTable.FirstName, INSTR(MyTable.FirstName, ' ', 1) + 1);

I just through this together and did NOT test it, but should be enough for you to start with...

Terry M. Hoey

 

[jebry]

Hi!

You can use the following two functions:

Public Function ExtractFirstName(strNameField as String) As String

Dim lngSpacePosition As Long
Dim strName As String

strName = Trim(strNameField)
lngSpacePosition = InStr(strName, " &quot
If lngSpacePosition = 0 Then
ExtractFirstName = strName
Else
ExtractFirstName = Left(strName, lngSpacePosition - 1)
End If

End Function

And

Public Function ExtractMiddleName(strNameField As String) As String

Dim lngSpacePosition As Long
Dim strName As String

strName = Trim(strNameField)
lngSpacePosition = InStr(strName)
If lngSpacePosition = 0 Then
ExtractMiddleName = ""
Else
ExtractMiddleName = Mid(strName, lngSpacePosition + 1, 1)
End If

End Function

You can use these two functions in the Update to field in your update query. One caution, these functions assume that you know for sure that there is only one space between the first name and middle initial any time a middle initial exists. If there is some doubt, then you will need another function to extract the extra spaces first. Like this:

Public Function ExtractSpaces(strWrongString As String) As String

Dim strRightString As String
Dim lngSpacePosition As Long
Dim lngStartPosition As Long

strRightString = Trim(strWrongString)
lngSpacePostion = InStr(strRightString)
Do Until lngSpacePosition = 0
If Mid(strRightString, lngSpacePosition, 1) = " " Then
strRightString = Left(strRightString, lngSpacePostion) & Mid(strRightString, lngSpacePosition + 2)
lngStartPosition = lngSpacePosition + 1
Else
lngStartPosition = lngSpacePosition + 1
End IF
lngSpacePosition = InStr(lngStartPostion, strRightString, " &quot
Loop

ExtractSpaces = strRightString

End Function

In the first to functions you call this function like this:

strName = ExtractSpaces(strNameField)

And you will get a trimmed string with the proper amount of spaces.

hth
Jeff Bridgham

 

[THoey]

Good reply Jeff. Badger8 will just want to make sure they update the middle name before they update the first name. Otherwise, they will lose all of their middle names... Terry M. Hoey

 

[jebry]

Hi Terry!

Thanks, there is something else I thought of later too. If the middle initial field is not a new field and there is data already there, badger8 will need to use an IIf statement to make sure no existing data is lost. Otherwise my ExtractMiddleName function will overwrite existing data with an empty string. Always one more pitfall than you see at first! X-)

Jeff Bridgham

 

[THoey]

Yep... I thought of that too, but ASSUMED that this was a NEW field. We both know what happens when one ASSUME's... Terry M. Hoey