UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formatting Text In Report, Access 2013    
 
   
Psycoperl
post Mar 12 2018, 10:16 AM
Post#1



Posts: 117
Joined: 11-March 15



Good Morning,
I have what should hopefully be a simple question. The data source where I am pulling names from does not contain a standard formatting of names, where some names could be in proper format (Capital First Letter with lowercase letters following) or could be in All caps/all lower. Is there a way to format the name field so that I can have it look properly formatted when printing the recipients name in a form letter (via a report)?

Thanks
Go to the top of the page
 
MadPiet
post Mar 12 2018, 10:26 AM
Post#2



Posts: 2,426
Joined: 27-February 09



use StrConv in your query or in the formatting of the field in your report.
https://support.office.com/en-us/article/st...1c-fd32734c92e0
Go to the top of the page
 
doctor9
post Mar 12 2018, 10:30 AM
Post#3


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Psycoperl,

If you can fix your source data, you should do so. Then, you should put controls in place that force new records to be properly formatted to start with.

The basic problem is that there are many names that do not have one capital letter followed by all lowercase letters. Like the last names "Dave DeBurgh" or "Betty Jo Van Den Langenberg". Or "Bob Jones III".

I believe there are a few utilities that can make some good guesses in our Code Archive like this one, but ideally you should have the names properly formatted in the tables. Then, if you want them to be in all caps, you can easily do that when printing or displaying on a form.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Psycoperl
post Mar 12 2018, 10:41 AM
Post#4



Posts: 117
Joined: 11-March 15



@Doctor9, unfortunately, the source data is from an external system which I have no control over. We receive data files from this system which are used in our Access based tools, which is why I need to do cleaning.

@MadPiet - Thank you I will look into this.
Go to the top of the page
 
MadPiet
post Mar 12 2018, 11:52 AM
Post#5



Posts: 2,426
Joined: 27-February 09



As Dennis pointed out, it won't be perfect. You might want to run updates against the tables the data is imported into. The fun part is when you have Irish names etc. It's not a perfect solution by a long shot.
Go to the top of the page
 
doctor9
post Mar 12 2018, 12:26 PM
Post#6


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Psycoperl,

In this case, I would say try creating a table of unusual names as you encounter them. Any name that doesn't fit the mold of "one word name with one capital letter and the rest are lowercase", add the name in all lowercase in the "lower case" field, and capitalized the way the person prefers in the "repaired case" field. Then, you can use a query that looks up the unusual names and repairs their case accordingly, and applies the "Proper Case" rule to the rest. As you encounter more troublesome names, add them to the table.

And try to communicate the problem to your data source to see if they can make things better on their end. It never hurts to ask. If the original data source is providing both all-lowercase AND all-uppercase AND mixed case names, they should be made aware of the issue.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
projecttoday
post Mar 12 2018, 08:38 PM
Post#7


UtterAccess VIP
Posts: 9,818
Joined: 10-February 04
From: South Charleston, WV


Dennis, why is "Betty Jo Van Den Langenberg" a problem for strConv? How would that be different from "John Smith"? And if suffixes are stored separately, "Bob Jones III" won't be a problem either. Even so, names like DeBurgh are rare. And I have actually seen "II" done as "Ii". I don't know if it's worth the trouble to go beyond strConv.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Mar 13 2018, 08:44 AM
Post#8


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Robert,

I wasn't talking about strConv I was talking about the assumption that a name would have ONE capital letter with that example. Sorry for the confusion.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
projecttoday
post Mar 13 2018, 09:35 AM
Post#9


UtterAccess VIP
Posts: 9,818
Joined: 10-February 04
From: South Charleston, WV


Isn't the issue more than one capital letter per word?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Mar 13 2018, 10:26 AM
Post#10


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Robert,

Yes, absolutely. It's a complex problem, which is why I suggested creating a table of the "exception" names.

If my previous posts seem like they're arguing against your posts, please know that's not my intent. My first post in this thread was a reply to the OP, not to you.

Hope this clears things up.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 10:40 AM