Text Format

[DaGoon]

To All,

I am looking for some help regarding the formatting of a text field on
a report. Here are the details:

I have a table named "tblMastPref" without the quotes, in that table
lies a text field named "TotalName: without the quotes. The text in
the TotalName field looks like this: SMITH, JOHN. It is driving me
nuts that this data was typed in all capital letters. When I use this
field in any of my reports it looks very unprofessional. I know I can
format this data in my report by entering a format command in the data
source for this field on my report. My question is can I please
trouble the group for what the syntax of the format command would be.

Thanks for the help...again!

 

[Jeff Boyce]

Depending on the version of Access you are using, you might be able to use
the StrConv() function, and the setting for "proper case".

Note, however, that this will NOT correctly format a name like McDonald or
Van de Wigge.

An alternate approach would be to clean up the data BEFORE sending to the
report. You could consider running update queries against your table to
change the name to proper case.

Note2: Storing the entire name in a single field could come back to bite
you! Consider splitting the name into FirstName, MiddleName and LastName
fields (and adding a NameSuffix field!).

 

[DaGoon]

Jeff,

Thanks for your help.

I am not familiar with the StrConv() function and I will look into it.
As far as cleaning up the data prior to sending it to the report would
be the best approach, I agree, but unfortunately there is some code in
the dBase that converts it to all caps. For the life of me I can't
find where though (I didn't write the dbase, but our company owns the
rights). If I sent you some code would you be willing to point me in
the right direction?

I also agree with the storing of the names. I didn't mention this but
I'm already combining the first and last names into the totalname
field with an update query

Bob

 

[Jeff Boyce]

Bob

It sounds like you're describing using dBase, rather than Access. Sorry,
but I have no experience there.

My recommendation was that you do NOT combine first/last names together.
You called it an "update query" -- in Access, this means you are changing
the data (updating it).

Do you mean you are combining them before displaying/reporting them?

 

[DaGoon]

Jeff,

I am using Access, I was just using "dBase" referring to my Access
"database".

Yes, the first and last name fields are being combined into the
TotalName field prior to displaying but, I'm still keeping the first
and last name fields separately in different fields within the same
table but I prefer to combine them for simplicity to use elsewhere in
certain reports.

Back to my original question... how do I format my "TotalName" field,
on a report, to capitalize the first letter of each word. The data in
the "TotalName" field currently looks like this: SMITH, JOSEPH

Any help that you can offer would be appreciated.

 

[John Vinson]

Back to my original question... how do I format my "TotalName" field,
on a report, to capitalize the first letter of each word. The data in
the "TotalName" field currently looks like this: SMITH, JOSEPH

Try:

TotalName: StrConv([LastName] & ", " & [FirstName], 3)

Note that this WILL give some erroneous names - Mcdonald, Maccay, De
La Torre (instead of de la Torre), Dagoon. It's probably better to use
an Update query to store the names in correct case to start with;
since Access searching and sorting are not case sensitive, there's no
benefit I can think of to storing them in all caps.

John W. Vinson[MVP]