Capitalize first character in a field

  • Thread starter Thread starter John
  • Start date Start date
J

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.
 
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.
Click to expand...

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
 
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


John Vinson said:
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.
Click to expand...

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
Click to expand...
 
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)



John said:
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


John Vinson said:
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.
Click to expand...

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
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
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
 
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?
Click to expand...

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.
 
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.
 
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.
Click to expand...

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,

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.
Click to expand...

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])
Click to expand...
 
"" Undefined function 'safeProper' in expression.
Click to expand...

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.
 
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
 
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"
Click to expand...

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.
 
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.
Click to expand...
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
 
and I still get erro message:

"Undefined function 'safeproper' in expression.

What have I missed?
Click to expand...

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.
 
Durned if I know! I see upthread that the Module is named
basSafeProper (not SafeProper), right?
Click to expand...

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.
Click to expand...

Debugged with no errors.
Also, try Compacting your database.
Click to expand...
Compacted. Error message persists.

Robin
 
John,

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

Robin
 
John,

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

Robin
 
Back
Top