Expanded Capital Macro

[Bruce Rodtnick]

I have been using the simple Capitol macro to capitol the first letter
of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones; MacDonald;
R.C. Jones; etc.) Is there a way to ensure that those special instances
can be properly capitalized? I know we're going to have to address each
instance, but it should be able to done, shouldn't it?

Bruce Rodtnick

 

[SimonM]

Look at the strConv function with the vbProperCase
argument. This will convert the first letter in every word
in the string to upper case.

For some of the other instances you mention you'd have to
code it. For instance, it would be quite easy to loop
through each character in a string and convert each
character that follows a - or a . to capital.

For anything more complicated the difficulty is the logic
behind it and the exceptions. For instance, you could code
to capitalise the first letter after "mac", but then when
someone has a name such as "Mack" then you wouldn't want
this.

 

[Mike Painter]

I have been using the simple Capitol macro to capitol the first letter
of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones; MacDonald;
R.C. Jones; etc.) Is there a way to ensure that those special instances
can be properly capitalized? I know we're going to have to address each
instance, but it should be able to done, shouldn't it?

Bruce Rodtnick

It will take a good bit of code to get all of them.
The code will have to have a lookup list for Mac and Mc etc as well as a
list that will save MD or DDS from becoming Md and Dds

 

[Dirk Goldgar]

I have been using the simple Capitol macro to capitol the first letter
of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.) Is there a way to ensure that those
special instances can be properly capitalized? I know we're going to
have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

http://www.mvps.org/access/strings/str0008.htm

 

[Bruce Rodtnick]

That code looks good, but I'm not able to get it to do anything. I'm using
=[mixed_case] in the After Update property in the text box LastName. I've
put the code in a separate module and I've also put it in the code under
that form. How should I address that function?

Bruce Rodtnick

I have been using the simple Capitol macro to capitol the first letter
of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.) Is there a way to ensure that those
special instances can be properly capitalized? I know we're going to
have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

http://www.mvps.org/access/strings/str0008.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

No, that wouldn't work. Set the AfterUpdate event *property* to "[Event
Procedure]", and would write the following event procedure:

'----- start of suggested code -----
Private Sub LastName_AfterUpdate()

If IsNull(Me!LastName.OldValue) Then
Me!LastName = mixed_case(Me!LastName)
End If

End Sub
'----- end of suggested code -----

I added that test for the OldValue property to allow you to manually fix
a name that is miscapitalized by the mixed_case() function. So if you
enter a last name for someone that previously didn't have one entered,
it will be adjusted according to the function's rules. But if you
change an existing last name, the function won't be called. You can
take out that conditional test if you like.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

That code looks good, but I'm not able to get it to do anything. I'm
using =[mixed_case] in the After Update property in the text box
LastName. I've put the code in a separate module and I've also put
it in the code under that form. How should I address that function?

Bruce Rodtnick

I have been using the simple Capitol macro to capitol the first
letter of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.) Is there a way to ensure that those
special instances can be properly capitalized? I know we're going
to have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

http://www.mvps.org/access/strings/str0008.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Bruce Rodtnick]

The .OldValue did not work on a new entry.  I'm getting a Runtime Error '438' Object does not support this method.  It works fine if I take out the conditional statement.

Am I missing something or is something not loaded right?

Bruce Rodtnick
 

Dirk Goldgar wrote: No, that wouldn't work.  Set the AfterUpdate event *property* to "[Event
Procedure]", and would write the following event procedure:

'----- start of suggested code -----
Private Sub LastName_AfterUpdate()

    If IsNull(Me!LastName.OldValue) Then
        Me!LastName = mixed_case(Me!LastName)
    End If

End Sub
'----- end of suggested code -----

I added that test for the OldValue property to allow you to manually fix
a name that is miscapitalized by the mixed_case() function.  So if you
enter a last name for someone that previously didn't have one entered,
it will be adjusted according to the function's rules.  But if you
change an existing last name, the function won't be called.  You can
take out that conditional test if you like.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Bruce Rodtnick" <[email protected]> wrote in message
news:[email protected]

That code looks good, but I'm not able to get it to do anything.  I'm
using =[mixed_case] in the After Update property in the text box
LastName.  I've put the code in a separate module and I've also put
it in the code under that form.  How should I address that function?

Bruce Rodtnick

Dirk Goldgar wrote:

"Bruce Rodtnick" <[email protected]> wrote in message
news:[email protected]

I have been using the simple Capitol macro to capitol the first
letter of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.)  Is there a way to ensure that those
special instances can be properly capitalized?  I know we're going
to have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

     http://www.mvps.org/access/strings/str0008.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The .OldValue did not work on a new entry. I'm getting a Runtime
Error '438' Object does not support this method. It works fine if I
take out the conditional statement. Am I missing something or is
something not loaded right?

That seems odd. Is this a bound form or an unbound form? Is the text
box bound or unbound? Are you sure you've got the control name correct?

 

[Bruce Rodtnick]

OK, the name of the text box is txtLastName, so I changed the code so that
all LastName (s) said txtLastName, and it worked well on a new entry, but it
didn't do anything when I edited it, which, I guess is what it's supposed to
do. Thing is, I was checking how it would work and it wasn't reading my mind
and doing what I wanted. If I wanted to have it work right I had to delete
the field, go out of the record and come back in. So I guess it's working
right. I'll try it for a while and see if I want to keep the Conditional
test. Thanks for your help.

Bruce Rodtnick

No, that wouldn't work. Set the AfterUpdate event *property* to "[Event
Procedure]", and would write the following event procedure:

'----- start of suggested code -----
Private Sub LastName_AfterUpdate()

If IsNull(Me!LastName.OldValue) Then
Me!LastName = mixed_case(Me!LastName)
End If

End Sub
'----- end of suggested code -----

I added that test for the OldValue property to allow you to manually fix
a name that is miscapitalized by the mixed_case() function. So if you
enter a last name for someone that previously didn't have one entered,
it will be adjusted according to the function's rules. But if you
change an existing last name, the function won't be called. You can
take out that conditional test if you like.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

That code looks good, but I'm not able to get it to do anything. I'm
using =[mixed_case] in the After Update property in the text box
LastName. I've put the code in a separate module and I've also put
it in the code under that form. How should I address that function?

Bruce Rodtnick

I have been using the simple Capitol macro to capitol the first
letter of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.) Is there a way to ensure that those
special instances can be properly capitalized? I know we're going
to have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

http://www.mvps.org/access/strings/str0008.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Bruce Rodtnick]

I'm intrigued with this code. I've been studying it and can't figure all
how it does it. I don't many strange capital names in my database yet so I
don't know how it works with other things.

It works on McDonald, but not on MacDonald. Do I need to write code for
each instance? And how would I do that?

Bruce Rodtnick

I have been using the simple Capitol macro to capitol the first letter
of words in my field and it works fine:
StrConv([Screen].[ActiveControl],3)

But some names have more than one capital in it (Smith-Jones;
MacDonald; R.C. Jones; etc.) Is there a way to ensure that those
special instances can be properly capitalized? I know we're going to
have to address each instance, but it should be able to done,
shouldn't it?

This page shows one approach:

http://www.mvps.org/access/strings/str0008.htm

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I'm intrigued with this code. I've been studying it and can't figure
all how it does it. I don't many strange capital names in my
database yet so I don't know how it works with other things.

It works on McDonald, but not on MacDonald. Do I need to write code
for each instance? And how would I do that?

In that particular module, you'd have to insert some more code in the
special_name() function. It could be modeled on the code already there,
which checks for "mc" and "o'", captializing the following letter.
However, you'd want to modify the basic idea for the "mac" prefix,
because there could be quite a number of names where you wouldn't want
to capitalize the letter after the "mac" (for example, "Mack", "Mackey",
"Macklin", "Mackenzie") and others where the inner capital may or may
not occur, such as (I think) "Macaulay" and "Macallister". There may be
enough exceptions to any rule involving "mac" that you'd do better just
to put in a few names to be always capitalized in a specific way, and
leave the rest alone.