How to display Proper Case in Access 2.0

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

Hi,

I have a Last Name field in which the original data is
uppercase. I would like this field to display in proper
case in my report. I have tried the following code
(recommended elsewhere on this newsgroup) = StrConv([Last
Name], vbProperCase). However, when I do this, Access
displays a dialogue box asking me to Enter Parameter Value
of StrConv.

I'm using Access 2.0.

Can anyone help? I'm very much a beginner, so please be
simple!
 
Hello Amy,

StrConv() is not available in Access 2.0. However, I have been using the
following function for years in Access 2.0 - it's not very elegant, but it
gets the job done.

Function MixCaseIt(strLineIn As String) As String
' The purpose of this function is to make a string which is solid caps
' into a mixed case string.


Dim i As Integer ' simple counter
Dim intSp As Integer ' how many spaces are there in the string
Dim strChr1 As String ' the first character in the string
Dim intUp As Integer ' a switch to indicate that a character should
be uppercase
Dim strChr As String ' collects individual character
Dim strLineOut As String ' collects each character after it has been
examined in loop

intSp = Len(Trim(strLineIn))

' lower case all characters to start
strLineIn = LCase(strLineIn)

' Upper the first character in string
strChr1 = UCase(Left$(strLineIn, 1))
strLineOut = strChr1
intUp = False

For i = 2 To intSp
If intUp = True Then
strChr = UCase(Mid$(strLineIn, i, 1))
Else
strChr = Mid$(strLineIn, i, 1)
End If
If Mid$(strLineIn, i, 1) = " " Then
intUp = True
Else
intUp = False
End If
strLineOut = strLineOut & strChr
Next i

MixCaseIt = strLineOut

End Function


hth,
 
Thanks for your help Cheryl, but I'm afraid I have
absolutely no idea what to do with that code! Where do I
put it? How does it relate to my Last Name text box in my
report?

Thank you,
Amy

-----Original Message-----
Hello Amy,

StrConv() is not available in Access 2.0. However, I have been using the
following function for years in Access 2.0 - it's not very elegant, but it
gets the job done.

Function MixCaseIt(strLineIn As String) As String
' The purpose of this function is to make a string which is solid caps
' into a mixed case string.


Dim i As Integer ' simple counter
Dim intSp As Integer ' how many spaces are there in the string
Dim strChr1 As String ' the first character in the string
Dim intUp As Integer ' a switch to indicate that a character should
be uppercase
Dim strChr As String ' collects individual character
Dim strLineOut As String ' collects each character after it has been
examined in loop

intSp = Len(Trim(strLineIn))

' lower case all characters to start
strLineIn = LCase(strLineIn)

' Upper the first character in string
strChr1 = UCase(Left$(strLineIn, 1))
strLineOut = strChr1
intUp = False

For i = 2 To intSp
If intUp = True Then
strChr = UCase(Mid$(strLineIn, i, 1))
Else
strChr = Mid$(strLineIn, i, 1)
End If
If Mid$(strLineIn, i, 1) = " " Then
intUp = True
Else
intUp = False
End If
strLineOut = strLineOut & strChr
Next i

MixCaseIt = strLineOut

End Function


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hi,

I have a Last Name field in which the original data is
uppercase. I would like this field to display in proper
case in my report. I have tried the following code
(recommended elsewhere on this newsgroup) = StrConv ([Last
Name], vbProperCase). However, when I do this, Access
displays a dialogue box asking me to Enter Parameter Value
of StrConv.

I'm using Access 2.0.

Can anyone help? I'm very much a beginner, so please be
simple!
Click to expand...


.
Click to expand...
 
Hi Amy,

First, copy all of the code into a Module. If you already have an existing
Module or Modules in your application, just open one in design view and
paste the code into it at the bottom. Or, if you have no Modules in your
application, in the Database window, click the Modules tab then the New
button. Paste the code into the Module and click the save button. You can
accept the default Module1 name. Then, close the module.

To use the function, I'd suggest starting off with a Query using the table
which contains your LastName field. In the Field: row of your query's
grid, type the following:

conLastName: MixCaseIT([LastName])

When you insert your LastName field in between the parentheses of the
function, you are "passing" it to the function so that the conversion can
take place. Now, run your query. You should see your last names converted
to Proper Case, where the first letter of each name is capitalized and the
rest are lower case. If the last name is composed of more than one word;
i.e., VAN DYKE, each distinct word will be converted (Van Dyke). If the
last name has embedded capital letters; i.e., MCCARTHY, these will not be
handled correctly and will be converted to: Mccarthy.

hth,


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Amy said:
Thanks for your help Cheryl, but I'm afraid I have
absolutely no idea what to do with that code! Where do I
put it? How does it relate to my Last Name text box in my
report?

Thank you,
Amy

-----Original Message-----
Hello Amy,

StrConv() is not available in Access 2.0. However, I have been using the
following function for years in Access 2.0 - it's not very elegant, but it
gets the job done.

Function MixCaseIt(strLineIn As String) As String
' The purpose of this function is to make a string which is solid caps
' into a mixed case string.


Dim i As Integer ' simple counter
Dim intSp As Integer ' how many spaces are there in the string
Dim strChr1 As String ' the first character in the string
Dim intUp As Integer ' a switch to indicate that a character should
be uppercase
Dim strChr As String ' collects individual character
Dim strLineOut As String ' collects each character after it has been
examined in loop

intSp = Len(Trim(strLineIn))

' lower case all characters to start
strLineIn = LCase(strLineIn)

' Upper the first character in string
strChr1 = UCase(Left$(strLineIn, 1))
strLineOut = strChr1
intUp = False

For i = 2 To intSp
If intUp = True Then
strChr = UCase(Mid$(strLineIn, i, 1))
Else
strChr = Mid$(strLineIn, i, 1)
End If
If Mid$(strLineIn, i, 1) = " " Then
intUp = True
Else
intUp = False
End If
strLineOut = strLineOut & strChr
Next i

MixCaseIt = strLineOut

End Function


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hi,

I have a Last Name field in which the original data is
uppercase. I would like this field to display in proper
case in my report. I have tried the following code
(recommended elsewhere on this newsgroup) = StrConv ([Last
Name], vbProperCase). However, when I do this, Access
displays a dialogue box asking me to Enter Parameter Value
of StrConv.

I'm using Access 2.0.

Can anyone help? I'm very much a beginner, so please be
simple!
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks again Cheryl.

However, creating a new module with this code in causes
compile errors throughout the database. I think I'll just
leave it!

Thanks anyway.
Amy
-----Original Message-----
Hi Amy,

First, copy all of the code into a Module. If you already have an existing
Module or Modules in your application, just open one in design view and
paste the code into it at the bottom. Or, if you have no Modules in your
application, in the Database window, click the Modules tab then the New
button. Paste the code into the Module and click the save button. You can
accept the default Module1 name. Then, close the module.

To use the function, I'd suggest starting off with a Query using the table
which contains your LastName field. In the Field: row of your query's
grid, type the following:

conLastName: MixCaseIT([LastName])

When you insert your LastName field in between the parentheses of the
function, you are "passing" it to the function so that the conversion can
take place. Now, run your query. You should see your last names converted
to Proper Case, where the first letter of each name is capitalized and the
rest are lower case. If the last name is composed of more than one word;
i.e., VAN DYKE, each distinct word will be converted (Van Dyke). If the
last name has embedded capital letters; i.e., MCCARTHY, these will not be
handled correctly and will be converted to: Mccarthy.

hth,


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thanks for your help Cheryl, but I'm afraid I have
absolutely no idea what to do with that code! Where do I
put it? How does it relate to my Last Name text box in my
report?

Thank you,
Amy

-----Original Message-----
Hello Amy,

StrConv() is not available in Access 2.0. However, I have been using the
following function for years in Access 2.0 - it's not very elegant, but it
gets the job done.

Function MixCaseIt(strLineIn As String) As String
' The purpose of this function is to make a string
Click to expand...
which
is solid caps
' into a mixed case string.


Dim i As Integer ' simple counter
Dim intSp As Integer ' how many spaces are there in the string
Dim strChr1 As String ' the first character in
Click to expand...
the
string
Dim intUp As Integer ' a switch to indicate
Click to expand...
that a
character should
be uppercase
Dim strChr As String ' collects individual character
Dim strLineOut As String ' collects each character after it has been
examined in loop

intSp = Len(Trim(strLineIn))

' lower case all characters to start
strLineIn = LCase(strLineIn)

' Upper the first character in string
strChr1 = UCase(Left$(strLineIn, 1))
strLineOut = strChr1
intUp = False

For i = 2 To intSp
If intUp = True Then
strChr = UCase(Mid$(strLineIn, i, 1))
Else
strChr = Mid$(strLineIn, i, 1)
End If
If Mid$(strLineIn, i, 1) = " " Then
intUp = True
Else
intUp = False
End If
strLineOut = strLineOut & strChr
Next i

MixCaseIt = strLineOut

End Function


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hi,

I have a Last Name field in which the original data is
uppercase. I would like this field to display in proper
case in my report. I have tried the following code
(recommended elsewhere on this newsgroup) = StrConv ([Last
Name], vbProperCase). However, when I do this, Access
displays a dialogue box asking me to Enter Parameter Value
of StrConv.

I'm using Access 2.0.

Can anyone help? I'm very much a beginner, so please be
simple!


.
Click to expand...
Click to expand...


.
Click to expand...
 
If you would care to post the error message you receive and indicate the
line on which it occurred, I'd be happy to help you work through correcting
it.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Amy said:
Thanks again Cheryl.

However, creating a new module with this code in causes
compile errors throughout the database. I think I'll just
leave it!

Thanks anyway.
Amy
-----Original Message-----
Hi Amy,

First, copy all of the code into a Module. If you already have an existing
Module or Modules in your application, just open one in design view and
paste the code into it at the bottom. Or, if you have no Modules in your
application, in the Database window, click the Modules tab then the New
button. Paste the code into the Module and click the save button. You can
accept the default Module1 name. Then, close the module.

To use the function, I'd suggest starting off with a Query using the table
which contains your LastName field. In the Field: row of your query's
grid, type the following:

conLastName: MixCaseIT([LastName])

When you insert your LastName field in between the parentheses of the
function, you are "passing" it to the function so that the conversion can
take place. Now, run your query. You should see your last names converted
to Proper Case, where the first letter of each name is capitalized and the
rest are lower case. If the last name is composed of more than one word;
i.e., VAN DYKE, each distinct word will be converted (Van Dyke). If the
last name has embedded capital letters; i.e., MCCARTHY, these will not be
handled correctly and will be converted to: Mccarthy.

hth,


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thanks for your help Cheryl, but I'm afraid I have
absolutely no idea what to do with that code! Where do I
put it? How does it relate to my Last Name text box in my
report?

Thank you,
Amy


-----Original Message-----
Hello Amy,

StrConv() is not available in Access 2.0. However, I
have been using the
following function for years in Access 2.0 - it's not
very elegant, but it
gets the job done.

Function MixCaseIt(strLineIn As String) As String
' The purpose of this function is to make a string which
is solid caps
' into a mixed case string.


Dim i As Integer ' simple counter
Dim intSp As Integer ' how many spaces are there
in the string
Dim strChr1 As String ' the first character in the
string
Dim intUp As Integer ' a switch to indicate that a
character should
be uppercase
Dim strChr As String ' collects individual
character
Dim strLineOut As String ' collects each character
after it has been
examined in loop

intSp = Len(Trim(strLineIn))

' lower case all characters to start
strLineIn = LCase(strLineIn)

' Upper the first character in string
strChr1 = UCase(Left$(strLineIn, 1))
strLineOut = strChr1
intUp = False

For i = 2 To intSp
If intUp = True Then
strChr = UCase(Mid$(strLineIn, i, 1))
Else
strChr = Mid$(strLineIn, i, 1)
End If
If Mid$(strLineIn, i, 1) = " " Then
intUp = True
Else
intUp = False
End If
strLineOut = strLineOut & strChr
Next i

MixCaseIt = strLineOut

End Function


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


message
Hi,

I have a Last Name field in which the original data is
uppercase. I would like this field to display in proper
case in my report. I have tried the following code
(recommended elsewhere on this newsgroup) = StrConv
([Last
Name], vbProperCase). However, when I do this, Access
displays a dialogue box asking me to Enter Parameter
Value
of StrConv.

I'm using Access 2.0.

Can anyone help? I'm very much a beginner, so please be
simple!


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top