Capitalization Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc then
LLC" but I am not sure how to do this.
 
Ken Getz has a function for this in his book "VBA Developers Handbook". It
uses a table that contains a list of items that should be treated
differently.

You can find more information here:
http://www.developershandbook.com/

The book listed here is the 2nd edition. I saw the function in the first
edition. It appears that Amazon.com may still have some of the first
editions. I don't know whether or not the 2nd edition has the function.

--
Wayne Morgan
Microsoft Access MVP


message
news:YW50aWdvbmU=.cc9ce4b1908df66428a93f8b8e752ce9@1072847548.cotse.net...
 
IF your data is such that everything after the comma should be capitalized and
there is always a comma then you can do a quick fix using the vba string
functions. HOWEVER, you might experience problems with something like John
Jones, Jr., MD or Joan Smith.

StrConv(Left(Field,Instr(1,Field,",")),3) For everything before the comma

UCase(Mid(Field,Instr(1,Field,",")+1)) For everything after the comma

Concatenate the two to get the full value

StrConv(Left(Field,Instr(1,Field,",")),3) & UCase(Mid(Field,Instr(1,Field,",")+1))

And you could even test for the existence of the comma first. The following
should all be on one line, but for ease of viewing it has been entered on
multiple lines.

IIF(Instr(1,Field,",") = 0,
StrConv(Field,3),
StrConv(Left(Field,Instr(1,Field,",")),3) &
UCase(Mid(Field,Instr(1,Field,",")+1)))

Wayne said:
Ken Getz has a function for this in his book "VBA Developers Handbook". It
uses a table that contains a list of items that should be treated
differently.

You can find more information here:
http://www.developershandbook.com/

The book listed here is the 2nd edition. I saw the function in the first
edition. It appears that Amazon.com may still have some of the first
editions. I don't know whether or not the 2nd edition has the function.

--
Wayne Morgan
Microsoft Access MVP

message
I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc then
LLC" but I am not sure how to do this.
Click to expand...
Click to expand...
 
DieSpammersDie said:
I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc then
LLC" but I am not sure how to do this.
Click to expand...


As John hinted at, there is no complete solution to
correctly capitalizing people's names. People can spell and
capitalize their name any way they choose, not to mention
that different cultures have different preferences. Short
of doing it correctly at data entry, the very best you can
do with a program is try to hit a significant majority of
the population represented in your database.

I wrote a fairly complex, pattern based, table driven
function to do this for an application once, but that
project was canceled before the application went into
general use (call it an alpha version so it may still have
bugs).

Only two other folks have ever looked at it, so independent
testing has been very limited. They didn't find any major
problems so I'm willing to expose it to more extensive use.
If you and a few other people can give it a going over and
let me know what problems I need to rectify, I'll try to
finish it up and publish it on The Access Web.

To use it for you immediate problem, you would at least have
to figure out how to integrate additional patterns into the
default list that I was testing with. If you think you're
up to it, post back or email me with your email address and
I'll send you a copy of the mdb that contains the pattern
table, code module and some test data.
 
Marshall,

I'd love to have a go at breaking it. ;-).





Marshall said:
I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc then
LLC" but I am not sure how to do this.
Click to expand...

As John hinted at, there is no complete solution to
correctly capitalizing people's names. People can spell and
capitalize their name any way they choose, not to mention
that different cultures have different preferences. Short
of doing it correctly at data entry, the very best you can
do with a program is try to hit a significant majority of
the population represented in your database.

I wrote a fairly complex, pattern based, table driven
function to do this for an application once, but that
project was canceled before the application went into
general use (call it an alpha version so it may still have
bugs).

Only two other folks have ever looked at it, so independent
testing has been very limited. They didn't find any major
problems so I'm willing to expose it to more extensive use.
If you and a few other people can give it a going over and
let me know what problems I need to rectify, I'll try to
finish it up and publish it on The Access Web.

To use it for you immediate problem, you would at least have
to figure out how to integrate additional patterns into the
default list that I was testing with. If you think you're
up to it, post back or email me with your email address and
I'll send you a copy of the mdb that contains the pattern
table, code module and some test data.
Click to expand...
 
WOW!!! Very nice. You are absolutely right...there is no sure way to
determine the correct capitalization. Your suggestion gets me 80% there,
but there are still some conditions that do not work. For example, your
suggestion gives me:

Acme Products, INC. When I really want Acme Products, Inc.
but
Acme Products, LLC is correct and your suggestion gives me that.

What if I only wanted to test for the condition of LLC or Llc and if LLC is
present then just make LLC all caps, but if it is INC. then it would
capitalize as Inc.

BTW...your solution got me much further then where I was...thank you.


IF your data is such that everything after the comma should be capitalized and
there is always a comma then you can do a quick fix using the vba string
functions. HOWEVER, you might experience problems with something like John
Jones, Jr., MD or Joan Smith.

StrConv(Left(Field,Instr(1,Field,",")),3) For everything before the comma

UCase(Mid(Field,Instr(1,Field,",")+1)) For everything after the comma

Concatenate the two to get the full value

StrConv(Left(Field,Instr(1,Field,",")),3) & UCase(Mid(Field,Instr(1,Field,",")+1))

And you could even test for the existence of the comma first. The following
should all be on one line, but for ease of viewing it has been entered on
multiple lines.

IIF(Instr(1,Field,",") = 0,
StrConv(Field,3),
StrConv(Left(Field,Instr(1,Field,",")),3) &
UCase(Mid(Field,Instr(1,Field,",")+1)))
I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc then
LLC" but I am not sure how to do this.
Click to expand...
Click to expand...
Click to expand...
 
Now you are getting into much tougher territory and will need a much more
complex algorithm. I would probably write a VBA function to handle this and the
function would need to have an exceptions table for things like , Inc.

If this is a one time thing and you want to do it permanently, I would just use
Access find and replace on the field in the table.

You could search for ", INC" and replace that with ", Inc"

Sorry, but for anything more complex I would see if I could get Marshall
Barton's code (mentioned elsewhere in the thread) and test it.


WOW!!! Very nice. You are absolutely right...there is no sure way to
determine the correct capitalization. Your suggestion gets me 80% there,
but there are still some conditions that do not work. For example, your
suggestion gives me:

Acme Products, INC. When I really want Acme Products, Inc.
but
Acme Products, LLC is correct and your suggestion gives me that.

What if I only wanted to test for the condition of LLC or Llc and if LLC is
present then just make LLC all caps, but if it is INC. then it would
capitalize as Inc.

BTW...your solution got me much further then where I was...thank you.
IF your data is such that everything after the comma should be capitalized and
there is always a comma then you can do a quick fix using the vba string
functions. HOWEVER, you might experience problems with something like John
Jones, Jr., MD or Joan Smith.

StrConv(Left(Field,Instr(1,Field,",")),3) For everything before the comma

UCase(Mid(Field,Instr(1,Field,",")+1)) For everything after the comma

Concatenate the two to get the full value

StrConv(Left(Field,Instr(1,Field,",")),3) & UCase(Mid(Field,Instr(1,Field,",")+1))

And you could even test for the existence of the comma first. The following
should all be on one line, but for ease of viewing it has been entered on
multiple lines.

IIF(Instr(1,Field,",") = 0,
StrConv(Field,3),
StrConv(Left(Field,Instr(1,Field,",")),3) &
UCase(Mid(Field,Instr(1,Field,",")+1)))
I am having a problem with capitalization in a report.

I am using strconv([Field],3) to have initial caps from the report I am
creating. However, there are items that I do not want initial caps, I
want
all caps. I have searched but cannot find an adequate response.

Here is a sample of my input data:

What I have: What I want: What I get:

BILL SMITH, MD Bill Smith, MD Bill Smith, Md
ACME SIGNS, LLC Acme Signs, LLC Acme Signes, Llc
JOHN ATTORNEY, PA John Attorney, PA John Attorney, Pa

Obviously, the MD, LLC, and PA should be allcaps. There is probably some
way to build a list of exceptions or write a program...sort of "if Llc
then
LLC" but I am not sure how to do this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top