ALL CAPS to PROPER

[Novice2000]

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper" so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks

 

[Douglas J. Steele]

Make a backup of the table first (just in case!), then try using the StrConv
function on it in an Update query.

Something like:

UPDATE MyTable SET MyField = StrConv([MyField], 3)

Replace MyTable and MyField with your actual names.

 

[Novice2000]

Pardon me for my complete ignorance, but I am really new to this. My table is
called "NoClaimsEver" and the field I want to convert is called "Primary
Name". I ran the query and the names are still in all caps.

Is this correct?

I have this "UPDATE NoClaimsEver SET Primary Name"=StrConv([Primary Name],3)

Make a backup of the table first (just in case!), then try using the StrConv
function on it in an Update query.

Something like:

UPDATE MyTable SET MyField = StrConv([MyField], 3)

Replace MyTable and MyField with your actual names.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper"
so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks

 

[Rick Brandt]

Pardon me for my complete ignorance, but I am really new to this. My
table is called "NoClaimsEver" and the field I want to convert is
called "Primary Name". I ran the query and the names are still in all
caps.

Is this correct?

I have this "UPDATE NoClaimsEver SET Primary Name"=StrConv([Primary
Name],3)

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary Name], 3)

 

[Sprinks]

The following function converts a string to proper case. In addition to
capitalizing the first letter and after a space it also capitalizes after an
open parentheses, a forward slash, and an open bracket character.

To use it, first cut and paste the function to a global module (Modules from
the database window). Then create an update query to set each field to the
result of calling the function on the field's current value. The example
below sets a CompanyName field in a table named Customer2:

UPDATE Customer2 SET Customer2.CompanyName = ProperCase([CompanyName]);

Please note there is no error-handling. If an error occurs, I'd suggest
returning the original string.

Hope that helps.
Sprinks

Public Function ProperCase(strParam As String) As String

Dim strWS As String ' Working string
Dim inti, intL, intChr As Integer
Dim blnCapNext As Boolean

' Convert string to lower case & determine length
strWS = LCase(strParam)
intL = Len(strWS)

' Set blnCapNext to True to capitalize first letter
blnCapNext = True

' Loop through each character in the string.
' If it is a space, open parentheses, forward slash, or open bracket,
' set blnCapNext to True to capitalize the next letter

For inti = 1 To intL
intChr = Asc(Mid(strWS, inti, 1))

Select Case intChr
'Space, open paren, forward slash, open bracket
Case 32, 40, 47, 91
blnCapNext = True
' Lower case letter
Case 97 To 122
If blnCapNext Then
strWS = Left(strWS, inti - 1) & _
Chr(intChr - 32) & Right(strWS, intL - inti)
End If
blnCapNext = False
Case Else
blnCapNext = False
End Select

Next inti
' Set function value to the value of the working string
ProperCase = strWS

End Function

 

[Novice2000]

I'm sorry. I appreciate your help, but you are still way over my head. Do I
paste this entire thing into the update field in the query or only part of it?

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary Name], 3)

Pardon me for my complete ignorance, but I am really new to this. My
table is called "NoClaimsEver" and the field I want to convert is
called "Primary Name". I ran the query and the names are still in all
caps.

Is this correct?

I have this "UPDATE NoClaimsEver SET Primary Name"=StrConv([Primary
Name],3)

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary Name], 3)

 

[Rick Brandt]

I'm sorry. I appreciate your help, but you are still way over my
head. Do I paste this entire thing into the update field in the query
or only part of it?

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary
Name], 3)

That is the entire SQL of the query. Open your query in SQL view and
replace everything there with the new statement, then go back to design view
and you will see what it would have looked like had you built it there.

We cannot post query design grids in these groups so you'll need to
familiarize yourself with SQL statements.

 

[Novice2000]

Hi Sprinks,

Thanks you. This is great information, and I am sure that I will be able to
understand it someday, but not today. They expect me to walk on water around
here, but I haven't even learned to wade yet. Can you break this down to
elemental terms? I can create an update query, but exactly what do I do
then??? I have tried entering the data shown, but nothing happens.

The following function converts a string to proper case. In addition to
capitalizing the first letter and after a space it also capitalizes after an
open parentheses, a forward slash, and an open bracket character.

To use it, first cut and paste the function to a global module (Modules from
the database window). Then create an update query to set each field to the
result of calling the function on the field's current value. The example
below sets a CompanyName field in a table named Customer2:

UPDATE Customer2 SET Customer2.CompanyName = ProperCase([CompanyName]);

Please note there is no error-handling. If an error occurs, I'd suggest
returning the original string.

Hope that helps.
Sprinks

Public Function ProperCase(strParam As String) As String

Dim strWS As String ' Working string
Dim inti, intL, intChr As Integer
Dim blnCapNext As Boolean

' Convert string to lower case & determine length
strWS = LCase(strParam)
intL = Len(strWS)

' Set blnCapNext to True to capitalize first letter
blnCapNext = True

' Loop through each character in the string.
' If it is a space, open parentheses, forward slash, or open bracket,
' set blnCapNext to True to capitalize the next letter

For inti = 1 To intL
intChr = Asc(Mid(strWS, inti, 1))

Select Case intChr
'Space, open paren, forward slash, open bracket
Case 32, 40, 47, 91
blnCapNext = True
' Lower case letter
Case 97 To 122
If blnCapNext Then
strWS = Left(strWS, inti - 1) & _
Chr(intChr - 32) & Right(strWS, intL - inti)
End If
blnCapNext = False
Case Else
blnCapNext = False
End Select

Next inti
' Set function value to the value of the working string
ProperCase = strWS

End Function

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper" so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks

 

[Douglas J. Steele]

Create a new query, and don't bother selecting any tables.

Click on the SQL button (or go into the SQL view by selecting it from the
View menu)

Paste what Rick gave you there, and run the query. Since you'll likely never
need to do this again, there's no point even saving the query.

One slight correction to what Rick gave, though. In Queries, you use ., not
!, so you want

UPDATE NoClaimsEver
SET [NoClaimsEver].[Primary Name]=StrConv([NoClaimsEver].[Primary Name], 3)

although the following will work as well

UPDATE NoClaimsEver
SET [Primary Name]=StrConv([Primary Name], 3)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry. I appreciate your help, but you are still way over my head. Do
I
paste this entire thing into the update field in the query or only part of
it?

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary Name],
3)

Pardon me for my complete ignorance, but I am really new to this. My
table is called "NoClaimsEver" and the field I want to convert is
called "Primary Name". I ran the query and the names are still in all
caps.

Is this correct?

I have this "UPDATE NoClaimsEver SET Primary Name"=StrConv([Primary
Name],3)

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=StrConv([NoClaimsEver]![Primary Name],
3)

 

[Sprinks]

Novice,

For your purposes, I'd use the built-in Strconv() function as Rick suggests.
I "rolled my own" function because I had several other characters afterwhich
I wanted to capitalize.

After making a backup copy of your table (Ctrl-C, Ctrl-V from the Table
window, and give it a new name), paste Rick's SQL into the SQL window.
Double-check that the spelling of the field is correct (i.e.., is it
"PrimaryName" or "Primary Name"), and press the red exclamation point icon on
the Query Design toolbar to execute it. Say Yes when Access asks you if you
wish to continue. All records should be updated to the result of the call of
the StrConv function.

If you wanted to use my custom function, after pasting the code to a module
and saving it, you'd replace the StrConv call to a call to my function,
removing the 2nd passed parameter:

UPDATE NoClaimsEver
SET [NoClaimsEver]![Primary Name]=ProperCase([NoClaimsEver]![Primary Name])

Paste it to the SQL window, and press the exclamation mark.

Sprinks

Hi Sprinks,

Thanks you. This is great information, and I am sure that I will be able to
understand it someday, but not today. They expect me to walk on water around
here, but I haven't even learned to wade yet. Can you break this down to
elemental terms? I can create an update query, but exactly what do I do
then??? I have tried entering the data shown, but nothing happens.

The following function converts a string to proper case. In addition to
capitalizing the first letter and after a space it also capitalizes after an
open parentheses, a forward slash, and an open bracket character.

To use it, first cut and paste the function to a global module (Modules from
the database window). Then create an update query to set each field to the
result of calling the function on the field's current value. The example
below sets a CompanyName field in a table named Customer2:

UPDATE Customer2 SET Customer2.CompanyName = ProperCase([CompanyName]);

Please note there is no error-handling. If an error occurs, I'd suggest
returning the original string.

Hope that helps.
Sprinks

Public Function ProperCase(strParam As String) As String

Dim strWS As String ' Working string
Dim inti, intL, intChr As Integer
Dim blnCapNext As Boolean

' Convert string to lower case & determine length
strWS = LCase(strParam)
intL = Len(strWS)

' Set blnCapNext to True to capitalize first letter
blnCapNext = True

' Loop through each character in the string.
' If it is a space, open parentheses, forward slash, or open bracket,
' set blnCapNext to True to capitalize the next letter

For inti = 1 To intL
intChr = Asc(Mid(strWS, inti, 1))

Select Case intChr
'Space, open paren, forward slash, open bracket
Case 32, 40, 47, 91
blnCapNext = True
' Lower case letter
Case 97 To 122
If blnCapNext Then
strWS = Left(strWS, inti - 1) & _
Chr(intChr - 32) & Right(strWS, intL - inti)
End If
blnCapNext = False
Case Else
blnCapNext = False
End Select

Next inti
' Set function value to the value of the working string
ProperCase = strWS

End Function

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper" so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks

 

[Novice2000]

Thanks all of you. I finally got it to work. Square peg in a round hole sort
of thing for me.

Hi Sprinks,

Thanks you. This is great information, and I am sure that I will be able to
understand it someday, but not today. They expect me to walk on water around
here, but I haven't even learned to wade yet. Can you break this down to
elemental terms? I can create an update query, but exactly what do I do
then??? I have tried entering the data shown, but nothing happens.

The following function converts a string to proper case. In addition to
capitalizing the first letter and after a space it also capitalizes after an
open parentheses, a forward slash, and an open bracket character.

To use it, first cut and paste the function to a global module (Modules from
the database window). Then create an update query to set each field to the
result of calling the function on the field's current value. The example
below sets a CompanyName field in a table named Customer2:

UPDATE Customer2 SET Customer2.CompanyName = ProperCase([CompanyName]);

Please note there is no error-handling. If an error occurs, I'd suggest
returning the original string.

Hope that helps.
Sprinks

Public Function ProperCase(strParam As String) As String

Dim strWS As String ' Working string
Dim inti, intL, intChr As Integer
Dim blnCapNext As Boolean

' Convert string to lower case & determine length
strWS = LCase(strParam)
intL = Len(strWS)

' Set blnCapNext to True to capitalize first letter
blnCapNext = True

' Loop through each character in the string.
' If it is a space, open parentheses, forward slash, or open bracket,
' set blnCapNext to True to capitalize the next letter

For inti = 1 To intL
intChr = Asc(Mid(strWS, inti, 1))

Select Case intChr
'Space, open paren, forward slash, open bracket
Case 32, 40, 47, 91
blnCapNext = True
' Lower case letter
Case 97 To 122
If blnCapNext Then
strWS = Left(strWS, inti - 1) & _
Chr(intChr - 32) & Right(strWS, intL - inti)
End If
blnCapNext = False
Case Else
blnCapNext = False
End Select

Next inti
' Set function value to the value of the working string
ProperCase = strWS

End Function

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper" so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks

 

[Rick Brandt]

Create a new query, and don't bother selecting any tables.

Click on the SQL button (or go into the SQL view by selecting it from
the View menu)

Paste what Rick gave you there, and run the query. Since you'll
likely never need to do this again, there's no point even saving the
query.
One slight correction to what Rick gave, though. In Queries, you use
., not !, so you want

UPDATE NoClaimsEver
SET [NoClaimsEver].[Primary Name]=StrConv([NoClaimsEver].[Primary
Name], 3)
although the following will work as well

UPDATE NoClaimsEver
SET [Primary Name]=StrConv([Primary Name], 3)

I never know what syntax to use in posts. In the query design grid it will
convert any dot notations to the bang notation I used which is why I posted
it that way. The design grid also seems to require including the table name
in the reference in places where SQL view would not.

 

[Douglas J. Steele]

Douglas J. Steele wrote:
The design grid also seems to require including the table name in the
reference in places where SQL view would not.

Can't say that I've noticed that, but I'll take your word.

 

[Access G-man]

I am SQL language impaired, but I just used this syntax, it works great.
Saved me a lot of time. Thank you for sharing your knowledge. All of you
contributed to this thread.
--
Grant
Stumblin'' and Bumblin'', but gettin it done!!

Make a backup of the table first (just in case!), then try using the StrConv
function on it in an Update query.

Something like:

UPDATE MyTable SET MyField = StrConv([MyField], 3)

Replace MyTable and MyField with your actual names.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have inherited an access table in which all of the entries (270,000 of
them) have been made in all caps. I need to convert the table to "proper"
so
that the first letter is capitalized and the remainder is lower case.

Can I just update the field somehow?? Thanks