Changing all CAPS, to Title Format

[Chuck712]

I have a database that uses Excel spreadsheets to update the tables. There is a field that contains the names of the department heads, that comes over in all caps. Does anyone know of a quick way for me to change all of these over to a title type format, without having to go back to the Excel spread sheet and re-enter them all? I would like to just update the table with a SQL statment. Any ideas?

 

[FoxProProgrammer]

By title type format, do you mean capitalize the first character of each name? Are there first names and last names separated by a space? dz
dzaccess@yahoo.com

 

[BlackKnight]

Hi,

You can use this UPDATE query which uses a function in a code module:


UPDATE tblDepartmentName SET tblDepartmentName.DepartmentName = TitleCase([DepartmentName]);
==========================


Public Function TitleCase(ByVal AStr As String) As String

'Convert every character to lowercase first. Trim any insignificant spaces.

AStr = LCase$(Trim$(AStr))

'Check for a significant space.
If InStr(Trim$(AStr), &quot; &quot <> 0 Then
'We know there is at least 1 significant space.
ChPos = InStrRev(AStr, &quot; &quot

Do While ChPos > 1
AStr = Replace(AStr, Mid$(AStr, ChPos + 1, 1), UCase$(Mid$(AStr, ChPos + 1, 1)))
'Look for another significant space.
ChPos = InStrRev(AStr, &quot; &quot;, ChPos - 1)
Loop

End If

AStr = UCase$(Mid$(AStr, 1, 1)) & Mid$(AStr, 2)
TitleCase = AStr

End Function

Have a good one!
BK

 

[Chuck712]

Thanks for the reply. I don't understand where to put the UPDATE query, though. This is not my strongest area.

 

[Chuck712]

When I run the update query, I get the error Undefined function 'TitleCase' in expression.

 

[lewis33]

Chuck,
In access, I usually run an update query with
convert case function like:
StrConv([DepartmentName],3)

It's simple and doesn't fix all the McDonald's and such.

 

[Chuck712]

Thanks. I'll try that one too! I still cannot get the query to recognize the TitleCase function in the module, though

 

[BlackKnight]

Hi,

Is the TitleCase() function in your module PUBLIC? Is the module in the same database as the update query?

Have a good one!
BK

 

[Chuck712]

Yes. It is a public function. Here is the SQL statment:
UPDATE ChapterRoster SET ChapterRoster.Advisor = TitleCase([Advisor]);

The table to update is named ChapterRoster, the field to update is Advisor.
(I know that I did not use good naming conventions, but I did this on the fly.)
Thanks for all the help, so far!

 

[BlackKnight]

HI,

I used your table name and field name. I changed the update query accordingly to this:

UPDATE ChapterRoster SET ChapterRoster.Advisor = TitleCase([Advisor]);

It worked correctly. You didn't answer my question last time...is the function TitleCase() in a module (module1) in the SAME database as ChapterRoster and the update query? If it isn't that could be the problem.

Have a good one!
BK

 

[Chuck712]

Hey,
Thanks for the help. Yes the module,table and update query are all in the same database. The module is named TitleCase, but I noticed that it reads [TitleCase(Code)] in title bar. Here is the first line of code for the module.

Public Function TitleCase(ByVal AStr As String) As String

Do I need to change the name of this?

 

[Chuck712]

Got it. Changed the name in the module properties to TitleCase() and it ran like a champ. Now I need to fine tune it, but thanks.
Chuck

 

[Chuck712]

Hi.
I don't know if any of you revisit a thread, but I have an interesting result from my update query. For the most part, it returns the expected results (changing the text in the table from all caps to title case), Unless the same letter appears in the name more than twice. For instance, the name GLORIA GOODING, comes back Gloria GoodinG, and PEGGY EGGERS comes back PEggy EggErs. ???? Anyone ever seen that before.
I am using the function that I got from Black Knight word for word.
Thanks everyone.