MAC's & Mc's!!!

[ARJakhu]

Hi all,

Does anyone know if VBA can be used to find names beginning with Mac or Mc and in the event that it does, repeats the record with the opposite, i.e. generates an alias. For example:

If McDonald is found, another record is added to the table (where all the other fields for the new record are equal to the original) and the name on the new record is MacDonald.

ope this makes sense!!!

Ram

 

[SgtJarrow]

The below code will do it for you......It is not all inclusive, but will catch most names correct.....I have some other code that requires you to create a table with the "odd-ball" names and captilizes according to this, but try this first....if it doesn't work for you, let me know and I get you the other, much-more detailed, code....

Function Proper(pstrFld As Control) As Variant
' CONVERTS first letter of each word to upper case
' RETURNS converted text value
' NOTE converts most proper names correctly e.g. McKinvoy, O'Connor
' -----------------------------------------------------------------------------------------------
Dim intArraySize As Integer
Dim intArrayPos As Integer
Dim strReturnVal As String

If IsNull(pstrFld) Then
Proper = Null
Exit Function
End If

intArraySize = Len(Trim(pstrFld)) ' set size of array

pstrFld = LCase(pstrFld) ' set all chrs to lowercase

ReDim strArray(intArraySize) ' size array to hold field

For intArrayPos = 1 To intArraySize ' fill the array with the field characters
strArray(intArrayPos) = Mid$(pstrFld, intArrayPos, 1)
Next intArrayPos

strReturnVal = UCase(strArray(1)) ' upper case the first character

If strArray(1) = "M" Then ' check if first name starts with Mac or Mc
If intArraySize > 1 Then ' make sure array is not 1 character only
If strArray(2) = "a" And strArray(3) = "c" Then
strArray(4) = UCase(strArray(4)) ' upper case 1st letter after Mac
End If
If strArray(2) = "c" Then
strArray(3) = UCase(strArray(3)) ' upper case 1st letter after Mc
End If
End If
Else
If strArray(1) = "O" Then ' check if name starts with O'
If strArray(2) = "'" Then
strArray(3) = UCase(strArray(3)) ' uppercase character after O'
End If
End If
End If

For intArrayPos = 2 To intArraySize ' go through the remaining letters
' check for separators , - or .
If strArray(intArrayPos) = " " Or strArray(intArrayPos) = "-" Or strArray(intArrayPos) = "." Then
strArray(intArrayPos + 1) = UCase(strArray(intArrayPos + 1)) ' upper case the character after the separator
' check for Mac or Mc again
If strArray(intArrayPos + 1) = "M" Then
If strArray(intArrayPos + 2) = "a" And strArray(intArrayPos + 3) = "c" Then
strArray(intArrayPos + 4) = UCase(strArray(intArrayPos + 4)) ' uppercase character after Mac
End If
If strArray(intArrayPos + 2) = "c" Then
strArray(intArrayPos + 3) = UCase(strArray(intArrayPos + 3)) ' uppercase character after Mc
End If
End If
If strArray(intArrayPos) = "O" Then ' check if name starts with O'
If strArray(intArrayPos + 1) = "'" Then
strArray(intArrayPos + 2) = UCase(strArray(intArrayPos + 2)) ' uppercase character after O'
End If
End If
End If
' add the next current character to the answer
strReturnVal = strReturnVal + strArray(intArrayPos)

Next intArrayPos
' return the answer

pstrFld = strReturnVal
End Function Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[ARJakhu]

Hi Robert,

This works but I'm really interested in exploring the other code you have.

Grateful if you would post it when get a chance.

Ram

 

[SgtJarrow]

ARJakhu....

I seemed to have misplaced this code (and the databse I had it in). I am going to have to look at my restore disk....will try to post it tomorrow.... Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[SgtJarrow]

found it....send me an email to the address in my sig block and I will send you the sample database.... Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com