Capitalize first character in a field

[John]

I have several fields that i would like to have the first letter capitalized
as they are being entered in my form and i would like them stored that
way in the underlying table.
Fields such as;
FirstName
LastName
etc...
Is there any way to force this format at the table level so that all forms
that are created will store the data in the same format.
What is the best way to achieve this
Right now I have no formats and my users are storing data in all kinds of
ways
any help is appreciated.

 

[John Vinson]

I have several fields that i would like to have the first letter capitalized
as they are being entered in my form and i would like them stored that
way in the underlying table.
Fields such as;
FirstName
LastName
etc...
Is there any way to force this format at the table level so that all forms
that are created will store the data in the same format.
What is the best way to achieve this
Right now I have no formats and my users are storing data in all kinds of
ways
any help is appreciated.

The StrConv function is handy for this.

Note that not ALL first and last names should be capitalized with the
first letter only: e.g.

Janice Lynn
McKee
van der Steen

Here's a bit of VBA code that I use in the AfterUpdate event of a
textbox to correct the capitalization. It only affects text that has
been entered in all lower case, so that the user can type in one of
the above and not have it messed up:

Private Function txtName_AfterUpdate()
If StrComp([txtName], LCase([txtName])) = 0 Then ' all lower case?
[txtName] = StrConv([txtName], vbProperCase)
End If
End Sub

 

[John]

Thanks John,
I found something similar in the Access2000 KB

LastName = StrConv(LastName, vbProperCase)

Your code and this code seem to operate the same.
Is yours supposed to be able to handle and "produce" the
examples you provided? i.e. McKee
What does the "IF" part of your code achieve?
Thanks

I have several fields that i would like to have the first letter capitalized
as they are being entered in my form and i would like them stored that
way in the underlying table.
Fields such as;
FirstName
LastName
etc...
Is there any way to force this format at the table level so that all forms
that are created will store the data in the same format.
What is the best way to achieve this
Right now I have no formats and my users are storing data in all kinds of
ways
any help is appreciated.

The StrConv function is handy for this.

Note that not ALL first and last names should be capitalized with the
first letter only: e.g.

Janice Lynn
McKee
van der Steen

Here's a bit of VBA code that I use in the AfterUpdate event of a
textbox to correct the capitalization. It only affects text that has
been entered in all lower case, so that the user can type in one of
the above and not have it messed up:

Private Function txtName_AfterUpdate()
If StrComp([txtName], LCase([txtName])) = 0 Then ' all lower case?
[txtName] = StrConv([txtName], vbProperCase)
End If
End Sub

 

[Douglas J. Steele]

John's code checks to see whether or not you've included some upper case
letters. If the entire string is lower case, then it uses the StrConv
function to convert so that the first letter is capitalized. If there are
any upper case characters in the string, it doesn't do anything to it. In
other words, if you type in McKee, it leaves it as McKee. However, if you
type in mckee, it converts it to Mckee.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks John,
I found something similar in the Access2000 KB

LastName = StrConv(LastName, vbProperCase)

Your code and this code seem to operate the same.
Is yours supposed to be able to handle and "produce" the
examples you provided? i.e. McKee
What does the "IF" part of your code achieve?
Thanks

I have several fields that i would like to have the first letter capitalized
as they are being entered in my form and i would like them stored that
way in the underlying table.
Fields such as;
FirstName
LastName
etc...
Is there any way to force this format at the table level so that all forms
that are created will store the data in the same format.
What is the best way to achieve this
Right now I have no formats and my users are storing data in all kinds of
ways
any help is appreciated.

The StrConv function is handy for this.

Note that not ALL first and last names should be capitalized with the
first letter only: e.g.

Janice Lynn
McKee
van der Steen

Here's a bit of VBA code that I use in the AfterUpdate event of a
textbox to correct the capitalization. It only affects text that has
been entered in all lower case, so that the user can type in one of
the above and not have it messed up:

Private Function txtName_AfterUpdate()
If StrComp([txtName], LCase([txtName])) = 0 Then ' all lower case?
[txtName] = StrConv([txtName], vbProperCase)
End If
End Sub

 

[John Vinson]

Thanks John,
I found something similar in the Access2000 KB

LastName = StrConv(LastName, vbProperCase)

Your code and this code seem to operate the same.
Is yours supposed to be able to handle and "produce" the
examples you provided? i.e. McKee
What does the "IF" part of your code achieve?
Thanks

The If() is there precisely to allow the user to manually enter
strings like McKee. If you just use the strConv() function, it will
convert mckee or MCKEE or McKee to Mckee; even if the user edits that
back to McKee, the AfterUpdate event will fire again and uncapitalize
the K (much to the annoyance of Mr. McKee!)

The If() uses the StrComp function to compare the user's entry to the
lower case translation; if they are identical, then the user entered
data in all lower case letters, and the code converts it. If the user
entered data in Mixed Case or ALL UPPER CASE, the code assumes that
they want it that way and leaves it alone.

This has the limitation that the user won't be able to enter e.e.
cummings or other all-lower case names; a separate Update query (or
just editing in the table datasheet) would be needed for such special
cases.

 

[Robin Chapple]

Several times a week I need to import data from a web based database
where members enter their details in a random manner. (I do not have
the authority to change the database to have formatted fields.)

I output member infromation from what I have dowloaded. It is random
upper and lower case.

How do I apply this code? Can I treat the whole database each time it
is dowloaded or do I apply the code to the report?

Thanks,

Robin Chapple

 

[John]

I've recently found this code on "The Access Web"
How do i go about implementing it for the individual text boxes.
I can't seem to get it to work

Here's the code
http://www.mvps.org/access/strings/str0008.htm

 

[John Vinson]

How do I apply this code? Can I treat the whole database each time it
is dowloaded or do I apply the code to the report?

Either. To do the former, you can run an Update query updating the
field using the posted function; to use it in a Report, set the
Control Source of a textbox in the report to a call to the function.

 

[Robin Chapple]

The Update Query is my choice. The field name is [LastName] and I have
changed your code to this:

If StrComp(([lastName]), LCase([lastName])) = 0 Then ' all lower case?
[lastName] = StrConv([lastName], vbProperCase)

I am getting an error message that I have an operand without an
operator.

Thanks

Robin.

 

[John Vinson]

The Update Query is my choice. The field name is [LastName] and I have
changed your code to this:

If StrComp(([lastName]), LCase([lastName])) = 0 Then ' all lower case?
[lastName] = StrConv([lastName], vbProperCase)

I am getting an error message that I have an operand without an
operator.

Ok... you're mixing VBA code with SQL code, they're like oil and
water! I wasn't clear in my suggestion, sorry!

Copy this routine into a new Module on the modules tab:

Private Function SafeProper(txtName As String) As String
If StrComp(txtName, LCase(txtName)) = 0 Then ' all lower case?
SafeProper = StrConv(txtName, vbProperCase)
Else
SafeProper = txtName
End If
End Sub

On the Update To line of the query put

SafeProper([lastName])

 

[Robin Chapple]

John,

As "" Just getting Started "" I am out of my depth.

I think that I have followed your instructions:

Here is the module code:

Private Function SafeProper(txtName As String) As String
If StrComp(txtName, LCase(txtName)) = 0 Then ' all lower case?
SafeProper = StrConv(txtName, vbProperCase)
Else
SafeProper = txtName
End If
End Sub

I have also tried substituting LastName for txtName.

Hers is the SQL

UPDATE tblDatabase SET tblDatabase.LastName = SafeProper([lastName]);

I have also tried:

UPDATE tblDatabase SET tblDatabase.LastName =
modSafeProper([lastName]);

I get the error message:

"" Undefined function 'safeProper' in expression.

Many thanks,

Robin

The Update Query is my choice. The field name is [LastName] and I have
changed your code to this:

If StrComp(([lastName]), LCase([lastName])) = 0 Then ' all lower case?
[lastName] = StrConv([lastName], vbProperCase)

I am getting an error message that I have an operand without an
operator.

Ok... you're mixing VBA code with SQL code, they're like oil and
water! I wasn't clear in my suggestion, sorry!

Copy this routine into a new Module on the modules tab:

Private Function SafeProper(txtName As String) As String
If StrComp(txtName, LCase(txtName)) = 0 Then ' all lower case?
SafeProper = StrConv(txtName, vbProperCase)
Else
SafeProper = txtName
End If
End Sub

On the Update To line of the query put

SafeProper([lastName])

 

[John Vinson]

"" Undefined function 'safeProper' in expression.

I suspect that you tripped over an annoying quirk which I should have
mentioned: when you save the Module into which you have put this code,
you must use some name *OTHER THAN* safeProper. The module can't have
the same name as the Sub.

Try changing the module's name to basSafeProper, or maybe
basStringUtilities - anything, just so it's unique.

 

[Robin Chapple]

The name was modSafeProper which I thought was different enough but I
have changed it to basProperUtility with the same error message.

"Undefined function basProperUtility in expression"

Robin

 

[John Vinson]

The name was modSafeProper which I thought was different enough but I
have changed it to basProperUtility with the same error message.

"Undefined function basProperUtility in expression"

AH!

No, you call the Function, you don't call the Module. THat is, if the
Module is named basProperUtility and the function is named SafeProper,
use

SafeProper([fieldname])

in your query.

 

[Robin Chapple]

AH!

No, you call the Function, you don't call the Module. THat is, if the
Module is named basProperUtility and the function is named SafeProper,
use

SafeProper([fieldname])

in your query.

I have now a query:

UPDATE tblDatabase SET tblDatabase.LastName = SafeProper([LastName]);

and the module is this:

Private Function SafeProper(LastName As String) As String
If StrComp(LastName, LCase(LastName)) = 0 Then ' all lower case?
SafeProper = StrConv(LastName, vbProperCase)
Else
SafeProper = LastName
End If
End Function

and I still get erro message:

"Undefined function 'safeproper' in expression.

What have I missed?

Thanks,

Robin

 

[John Vinson]

and I still get erro message:

"Undefined function 'safeproper' in expression.

What have I missed?

Durned if I know! I see upthread that the Module is named
basSafeProper (not SafeProper), right?

Try opening the module in design mode and selecting Debug... Compile
<your database>. Do you get any compile errors? They'll need to be
fixed.

Also, try Compacting your database.

 

[Robin Chapple]

Durned if I know! I see upthread that the Module is named
basSafeProper (not SafeProper), right?

As it is not called by the module name I have returned it to my habit
of mod as the prefix. So it is modSafeProper.

Try opening the module in design mode and selecting Debug... Compile
<your database>. Do you get any compile errors? They'll need to be
fixed.

Debugged with no errors.

Also, try Compacting your database.

Compacted. Error message persists.

Robin

 

[Robin Chapple]

John,

I was advised by EE to make the module "public" instead of "private "
and all is well.

Robin

 

[Robin Chapple]

John,

I was advised by EE to make the module "public" instead of "private "
and all is well.

Robin