Change case, hyphenated last name? 2

[HenryAnthony]

Hi,

I get a list of names that are all upper case. No problem to change to proper case except for hyphenated last names. Any ideas on searching for a character to the right of a hyphen and changeing it to upper case. Please specify Access version of your solution.

Best regards,

Henr¥

 

[Oppenhiemer]

Hi Henr¥ -

I suggest you use the InStr function (which is available in most versions of Access I think.)

Opp.

 

[SBendBuckeye]

You might also want to check for an asterisk or space such as O'Donnell and Mac Neely. I believe there are a couple FAQs that deal with this

Use code as below:

Function ProperCase(strInputName as String) as String
dim strTemp as String
dim intWhereAt as Integer

strTemp = ""

If IsNull(strInputName) Or Len(StrInputName) = 0 Then
ProperCase = strTemp
Exit Function
End If

strTemp = Trim$(strInputName)

intWhereAt = InStr$(strInputName, &quot;-&quot <== reversed ??

'Break it apart up to the dash, upcase the next byte
'and then append the rest of the string
If intWhereAt > 0 Then
strTemp = Left$(strTemp, intWhereAt) _
& UCase$(Mid$(strTemp, intWhereAt + 1, 1)) _
& Mid$(strTemp, intWhereAt + 2)
End If

intWhereAt = InStr$(strTemp, &quot;'&quot <== reversed ??

'Break it apart up to the apostrophe, upcase the next
'byte and then append the rest of the string
If intWhereAt > 0 Then
strTemp = Left$(strTemp, intWhereAt) _
& UCase$(Mid$(strTemp, intWhereAt + 1, 1)) _
& Mid$(strTemp, intWhereAt + 2)
End If

intWhereAt = InStr$(strTemp, &quot; &quot <== reversed ??

'Break it apart up to the space, upcase the next byte
'and then append the rest of the string
If intWhereAt > 0 Then
strTemp = Left$(strTemp, intWhereAt) _
& UCase$(Mid$(strTemp, intWhereAt + 1, 1)) _
& Mid$(strTemp, intWhereAt + 2)
End If

'Check for Mc
If Left$(strTemp, 2) = &quot;Mc&quot; Then
strTemp = Left$(strTemp, 2) _
& UCase$(Mid$(strTemp, 3, 1) _
& Mid$(strTemp, 4)
End If

'Check for Mac
If Left$(strTemp, 3) = &quot;Mac&quot; Then
strTemp = Left$(strTemp, 3) _
& UCase$(Mid$(strTemp, 4, 1) _
& Mid$(strTemp, 5)
End If

ProperCase = strTemp

End Function

Have Fun and Enjoy!

 

[HenryAnthony]

Hi SBendBuckeye,

Thanks for the suggestion.

I also found the following code (see below) that's supposed to handle all this but I do not know how to call it in the query. Any suggestions? Thanks in advance.

Best regards,

Henr¥

'************** Code Start *************
'This code was originally written by Jay Holovacs.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Jay Holovacs
'
Public Function mixed_case(str As Variant) As String
'returns modified string, first character of each word us uppercase
'all others lower case
Dim ts As String, ps As Integer, char2 As String
If IsNull(str) Then
mixed_case = &quot;&quot;
Exit Function
End If
str = Trim(str) 'added 11/22/98
If Len(str) = 0 Then
mixed_case = &quot;&quot;
Exit Function
End If
ts = LCase$(str)
ps = 1
ps = first_letter(ts, ps)
special_name ts, 1 'try to fix the beginning
Mid$(ts, 1) = UCase$(Left$(ts, 1))
If ps = 0 Then
mixed_case = ts
Exit Function
End If
While ps <> 0
If is_roman(ts, ps) = 0 Then 'not roman, apply the other rules
special_name ts, ps
Mid$(ts, ps) = UCase$(Mid$(ts, ps, 1)) 'capitalize the first
letter
End If
ps = first_letter(ts, ps)
Wend
mixed_case = ts
End Function
Private Sub special_name(str As String, ps As Integer)
'expects str to be a lower case string, ps to be the
'start of name to check, returns str modified in place
'modifies the internal character (not the initial)
Dim char2 As String
char2 = Mid$(str, ps, 2) 'check for special cap rules
If (char2 = &quot;mc&quot; Or char2 = &quot;o'&quot And Len(str) > ps + 1 Then 'Mc form
Mid$(str, ps + 2) = UCase$(Mid$(str, ps + 2, 1))
End If
End Sub
Private Function first_letter(str As String, ps As Integer) As Integer
'ps=starting point to search (starts with character AFTER ps)
'returns next first letter, 0 if no more left
'modified 6/18/99 to handle hyphenated names
Dim p2 As Integer, p3 As Integer, s2 As String
s2 = str
p2 = InStr(ps, str, &quot; &quot 'points to next blank, 0 if no more
p3 = InStr(ps, str, &quot;-&quot 'points to next hyphen, 0 if no more
If p3 <> 0 Then
If p2 = 0 Then
p2 = p3
ElseIf p3 < p2 Then
p2 = p3
End If
End If
If p2 = 0 Then
first_letter = 0
Exit Function
End If
'first move to first non blank, non punctuation after blank
While is_alpha(Mid$(str, p2)) = False
p2 = p2 + 1
If p2 > Len(str) Then 'we ran off the end
first_letter = 0
Exit Function
End If
Wend
first_letter = p2
End Function
Public Function is_alpha(ch As String)
'returns true if this is alphabetic character
'false if not
Dim c As Integer
c = Asc(ch)
Select Case c
Case 65 To 90
is_alpha = True
Case 97 To 122
is_alpha = True
Case Else
is_alpha = False
End Select

End Function
Private Function is_roman(str As String, ps As Integer) As Integer
'starts at position ps, until end of word. If it appears to be
'a roman numeral, than the entire word is capped in passed back
'string, else no changes made in string
'returns 1 if changes were made, 0 if no change
Dim mx As Integer, p2 As Integer, flag As Integer, i As Integer
mx = Len(str) 'just so we don't go off the edge
p2 = InStr(ps, str, &quot; &quot 'see if there is another space after this word
If p2 = 0 Then
p2 = mx + 1
End If
'scan to see if any inappropriate characters in this word
flag = 0
For i = ps To p2 - 1
If InStr(&quot;ivxIVX&quot;, Mid$(str, i, 1)) = 0 Then
flag = 1
End If
Next i
If flag Then
is_roman = 0
Exit Function 'this is not roman numeral
End If
Mid$(str, ps) = UCase$(Mid$(str, ps, p2 - ps))
is_roman = 1
End Function
'************** Code End *************

 

[SBendBuckeye]

Henry,

The function must be placed in a module (has to be public I think). You then put the function in your query definition in design view using the builder. It would be something similar to =mixed_case(fieldname). I have done this at home if I can find it. Also, if any of your field names can contain null, you would want to include iif(isnull(fieldname),&quot;&quot;,fieldname) in your function call.

If you post your email address and I can find it, I'll send you an example.

Good Luck!

 

[Oppenhiemer]

Personally I would build the query in VBA code and call the function for the field values through that.

Opp.

 

[HenryAnthony]

Hi SBendBuckeye,

stasekh@trinity-health.org

Thanks for all the help.

 

[Quehay]

There's a built-in way to convert to initial caps (like Oracle InitCap function):
[tt]
x = StrConv(&quot;warren zevon&quot;, vbProperCase)
[/tt]

 

[HenryAnthony]

Quehay,

Thanks, actually I know that one already. I don't think it will work with hyphenated names but I will check.

Best regards,

Henr¥

 

[HenryAnthony]

SBendBuckeye,

I received your database but could not open it. I use Access 97, could yours be 2k? Thanks anyway. I also tried to email this response to you but it came back as undelivereable. As for Trinity, I work for Care Choices HMO and PPO, a Trinity member in Farmington Hills, Michigan (just outside Detroit).

 

[SBendBuckeye]

Henry,

Check your email. I sent you the pieces in Excel and a text file so you can set it up yourself.

Good Luck!