Input Mask for Last name

I want to use an input mask to automatically capitalize the first letter of a last name.  However I don't want to change the case of any of the other letters (e.g. to handle McClung as a last name).  What should I use as the input mask?

Thanks,


Matt
LVL 1
MatthewLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevenshlCommented:
Hi MatthewL,

>L<??????????????;;_

This works as an input mask which makes the entry of the first letter of a last name mandatory.

>?<??????????????;;_

This works as an input mask which would allow entry of any last name to be optional.

The number of question marks entered controls the number of letters allowed in the last name.

Hope this helps.
stevenshl
0
BrianWrenCommented:
Although as steven says, you can force the last letter, what you are asking cannot be done as a mask.

What you need to do, is after the name has been entered, examine it with code, modify it, and put the modified results in back into the control.

You will need to come up with all of the contingencies, and handle them individually, eg:

Find the first space in the string.
If there're no spaces, exit
If there's a letter after that space, capitalize it.
If the next letter is 'c', capitalize the one after that.
If 'Mac' is found, capitalize the 4th letter
etc.,
etc.,
etc.

Brian
0
MacRenaCommented:
Hey Brian,

Also, if Mc is found, capitalize the Third Letter!

MatthewL,
This kind of special case is not dealt with in the following routine, but it works pretty well.

Good Luck,

Mac

'**** Begin Code ****
Function gfnProperName(strNameString)
' Function will make the first character of each word in the passed string uppercase.
' Disable w/ extra space if first character should not be Capitalized
' (i.e.  "Charles  duPont Morgan" needs 2 spaces between first and second names. Third name will still be cpaitalized, but the P will need more coding)


On Error GoTo jump
'dimention & initialize variables
    Dim strName As String                           'Name as input
        strName = strNameString                     'passed from Calling Procedure
    Dim intNumberLetters As Integer                 'total number of characters in strName
        intNumberLetters = Len(LTrim(strName))      '(will be set at 50 max.)
    Dim strConName As String                        'will be constructed Name
        strConName = ""
    Dim intI As Integer
        intI = 1
    Dim intJ As Integer
        intJ = 0
    Dim strLetter(50) As String                     'to hold individual letters of Name
        For intJ = 0 To 49                          'initialize to null
            strLetter(intJ) = ""
        Next
'***************************************************************************************

        For intJ = 1 To intNumberLetters                'set to individual characters
            If Mid(strName, intJ, 1) = "_" Then Exit For 'underscore character is past end of name
            strLetter(intJ) = Mid(strName, intJ, 1)     'loads letters & spaces into strLetter()
        Next
        intJ = 1
        strConName = UCase$(strLetter(intJ))            'make first letter UCASE
                                                        'construct the rest
    For intJ = 2 To intNumberLetters                    'look at each concecutive letter,...
                                                        ' UCASE if after space or quote.
        If strLetter(intJ) = " " Or (strLetter(intJ) = Chr(34) And strLetter(intJ + 1) <> " ") Then 'for "Nick Names"
            strConName = strConName + strLetter(intJ)   ' constructed name and capitalize...
            intJ = intJ + 1                             ' next letter.
            strLetter(intJ) = UCase$(strLetter(intJ))
        End If
        strConName = strConName + strLetter(intJ)       'add the letter to constructed name
    Next                                                '
   
    gfnProperName = Trim(strConName)                 'Function result = constructed name
Exit Function

jump:
MsgBox "gfnProper - Error # " & Err & " Occured", vbOKOnly     'if error, show error # and End
End Function     '**** End Code ****
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrianWrenCommented:
Hey your own self Mac...

Listen:
I meant that the second letter being 'c' should capitalize for 'Mc', but of course, that would be bogus for last names like, oh..., I don't know.. like 'Ecclesiaticus...'

Regarding your function, it doesn't reset the error, so the routine that called this will have an error extant when this function is over.  You really should put an error handler, put code above it that exits so that you will not hit the 'Resume without Error' error, and then resume to clear the error:

    .
    .
    .
Func_Res:
    Exit Function

jump:
    MsgBox "gfnProper - Error # " & Err & " Occured"  ' vbOKOnly is the default..Not Nec.
    Resume Func_Res

End Function     '**** End Code ****
0
JimMorganCommented:
RTFQ - Matt said that he didn't want to change the case of any other letters in the name.  He just wants to be sure that the first letter is capitalized.

At first glance, stevenshl suggestion would appear to be right but this will force the first letter to be caps and all the rest of the letters to be lower case.

We don't know if the Last name will be in a field by itself or part of a complete name.

My recommendation is for data entry purposes to use two fields: First Name/Middle Initial and Last Name.  In the Before Update event of Last Name:

     If IsNull(LastName) Then Exit Sub

     LastName = UCase$(Left$(LastName, 1)) & Mid$(LastName, 2)

This would be the only way to met the conditions set in the question.  Then if he wants to combine the fields into a FullName field, concate the two fields.

Jim
0
tomk120999Commented:
Brian, we are back to Read The (What Kind of?) (Name your poison) type of acronym. :o))  Sorry, couldn't resist.  Cheers!
0
JimMorganCommented:
RTFQ > Read The Frigging Question

Used to put it across the top of every test paper that I did in school so that I would resist wasting time solving the problem until I thoroughly understood what the problem was.

Changed it around for my consulting service - ATEQ > Ask The Embarassing Questions.

Jim
0
MatthewLAuthor Commented:
Hi Jim

I put your function into the beforeUpdate event of a form, but I get a compile error (Error  #2215) on this line
 LastName = UCase$(Left$(LastName, 1))& Mid$(LastName, 2)

Any ideas?

Thanks,

Matt
0
tomk120999Commented:
Hello, MatthewL, do I see an argument missing in the Mid(String,Start,Characters) function?  Is that a typo or is it a difficulty?

good luck as usual...
0
JimMorganCommented:
Is LastName the name of the form object?  If LastName is empty or null then you might get an error.

    Sub LastName_BeforeUpdate()
         If IsNull(LastName) Then Exit Sub
         LastName = UCase$(Left$(LastName, 1)) & Mid$(LastName, 2)
    End Sub

You don't need the third argument 'Characters' mentioned in Tom's comment.

This should work even if the value is null but it is prudent programming.

The error that you mentioned indicates that an object is missing.  The only object here would be LastName.  Yet at the same time, if the form object does not exist, so double check that the name of the object matches the object in the expression.

Jim
0
BrianWrenCommented:
The problem with

LastName = UCase$(Left$(LastName, 1))& Mid$(LastName, 2)

is that

UCase$(Left$(LastName, 1))&  needs to be
UCase$(Left$(LastName, 1)) & ...

Mid$() reads from the start character, (the 2nd argument), to the end of the string, if no 3rd argument is provided.

Value: .02¢ . . .

Brian
0
MatthewLAuthor Commented:
Hi Jim

I redid the code you suggested.  The problem was that I had bumbled in the expression builder, and then written it in the code builder which left an unfilled expression in the event field.

Thanks for everyones help,

Matt

P.S. Jim could you answer please, there isn't an option for me to select you automatically.

0
JimMorganCommented:
I'll answer but there is an option for you select a comment automatically, unless you are working in the old-look site view.  On the right of each comment line is "Accept this comment as an Answer".  Selecting this will allow you do to just that.

Glad that I could be of help.  Sometimes the simplest solution is the best.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.