Converting All Upper to Upper/Lower

[Guest]

Our data is coming in all upper, however when we output the data to convert
and load into our payroll system we need the name to be Upper/lower. I have
been looking with no luck.

If anyone can help me here I sure would appreciate it.

Thanks
Vivian

 

[fredg]

Our data is coming in all upper, however when we output the data to convert
and load into our payroll system we need the name to be Upper/lower. I have
been looking with no luck.

If anyone can help me here I sure would appreciate it.

Thanks
Vivian

Look up StrConv() in VBA Help.
= StrConv([YourField],3)

This will not properly capitalize names such as MacDonald, van den
Steen, Smith-Barney, or O'Brien, etc.

 

[Guest]

StrConv("Name",vbProperCase) converts the 1st letter of each word to UPPER case

 

[Guest]

Thanks for replying so quickly. I found that statement, and have been
messing with it for an hour now. Where do I put this string at? I also would
assume when you say "name" it is [Name] (the field name in the table?)

Thanks

 

[Guest]

Fred

I tried yours and it work great. Thanks for everyones help.

Our data is coming in all upper, however when we output the data to convert
and load into our payroll system we need the name to be Upper/lower. I have
been looking with no luck.

If anyone can help me here I sure would appreciate it.

Thanks
Vivian

Look up StrConv() in VBA Help.
= StrConv([YourField],3)

This will not properly capitalize names such as MacDonald, van den
Steen, Smith-Barney, or O'Brien, etc.

 

[Guest]

Fred,

I just brought our whole file in and run it against the string. When I had
MACDONALD, it did Macdonald. It only did the 2nd half if there was a
space,which is OK by me. By biggest problem with this group, for some reason
they have a lot of employee's with - names.. example 'Adwetewa-badu'. Is
there anyway to get the b in badu in Caps?

Thanks

 

[fredg]

Fred,

I just brought our whole file in and run it against the string. When I had
MACDONALD, it did Macdonald. It only did the 2nd half if there was a
space,which is OK by me. By biggest problem with this group, for some reason
they have a lot of employee's with - names.. example 'Adwetewa-badu'. Is
there anyway to get the b in badu in Caps?

Thanks

As I indicated in my reply, StrConv will not properly capitalize words
with more than one capital in it.. McDonald, O'Brien, IBM, etc. nor
names that should not be capitalized, i.e. van der Meer, e.e.
cummings, etc.

You need to create a table with the words that need special handling.

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text Datatype
TableName: tblExceptions

Enter as many known name exceptions as you can. (And don't forget
'Adwetewa-Badu')

====
Paste this function into a new module.

Public Function ConvExceptions(StringIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn
&
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
" &
Chr(34) & StringIn & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(StringIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

Or use it in an Control's AfterUpdate event:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If


Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations of
names, O'Connor and O'connor are both correct, depending upon the
individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."