Capitalize text in a field

[©®]

I am struggling in Access 2007 to get the field "Movie name" to
capitalize the movie title.
I did a search and came up with "msoCaseTitle" but have no idea where to
put this in the database design view.
I see that there is an option to use ">" or "<" but I want every word
capitalized. Is this possible?
Field name: Movie title
Data type: Text
Field size: 255

Thanks very much.

 

[Rui]

you are looking convert the text into proper case.

try

StrConv([text goes here], vbProperCase)


cheers
Rui

 

[Ken Sheridan]

Add the following procedure to a standard module:

Public Sub ConvertToCaps(KeyAscii As Integer)
' Converts text typed into control to upper case

On Error GoTo Err_Handler

Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")",vbExclamation, "Error"

End Sub

If the module is a new one save it under a different name to the procedure,
e.g. mdlTextUtilities. Then in the KeyPress event procedure of any control
whose input you wish to force to upper case call the procedure with:

ConvertToCaps KeyAscii

BTW, unlike most routines, you can't change the above procedure to a
function and call it directly from a control's properties sheet as the
KeyPress event property, passing the KeyAscii value as an argument. It has
to be called in the event procedure.

The text will be converted to upper case as the user enters each character.
If you have existing values in the table in lower or mixed case you can
easily change them to upper case with an update query:

UPDATE [YourTableNameGoesHere]
SET [Movie Name] = UCASE([Movie Name]);

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Having seen Rui's reply, do you want the whole text capitalized, or converted
to Proper case? Mine gives you the former, his the latter. Note that you
can't use the vbProperCase constant if you call the function in a query, you
must use its numeric value: strConv[Movie Title], 3)

In a form you'd call the strConv function in the AfterUpdate procedure of
the Movie Title control. In this case you can use the constant:

[Movie Title] = strConv[Movie Title], vbProperCase)

Ken Sheridan
Stafford, England

 

[Rui]

I was just replying for the

"... or "<" but I want every word
capitalized. Is this possible? ..."

 

[©®]

Whoa! What you all posted seems waaaay over my head.
This is what I would like to accomplish:

If the movie title is:
One flew over the cuckoos nest
the result needs to be:
One Flew Over The Cuckoos Nest

Sorry everyone, but I am an Access newbie so I have *no* idea where you
even put all that info you gave me.
Thanks

 

[Ken Sheridan]

Whoa! What you all posted seems waaaay over my head.
This is what I would like to accomplish:

If the movie title is:
One flew over the cuckoos nest
the result needs to be:
One Flew Over The Cuckoos Nest

Sorry everyone, but I am an Access newbie so I have *no* idea where you
even put all that info you gave me.
Thanks

 

[Ken Sheridan]

Both my last reply and the latest poster's seem to have got lost along the
line somewhere. I probably would have said something like this:

1. To capitalise the titles as they are entered in you data entry form put
the following code in the Movie Name control's AfterUpdate event procedure:

Dim ctrl As Control

Set ctrl = Me.ActiveControl

ctrl = StrConv(ctrl, vbProperCase)

To do this select the Movie Name control in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the lines of code between these two
existing lines.

When a user enters a title, as soon as the control is updated, by pressing
the enter key, tabbing off the control, moving to another record or closing
the form for instance, the value will be converted to proper case. Data
should never be entered or edited in raw datasheet view, always via forms!


2. To convert all existing Movie Name values in the table to proper case
create an update query based on the table. To do this:

2.1 Select 'New' in the queries page of the database window.

2.2 Select 'design view'

2.3 Select you table in the 'Show table' dialogue, click 'Add' and then
close the dialogue.

2.4 Double click on the Movie Name field from the table in the query design
window. It will be added to the design grid.

2.4 From the Query menu select 'Update query'.

2.5 In the 'Update To' row of the Movie Name column in the query design
grid enter:

StrConv([Movie Name], 3)

Movie Name must be the exact name of the field including the space.

2.6 From the Query menu select 'Run' and confirm you want to go ahead when
prompted. Its not necessary to save the query, but you can do so for later
re-use if you wish.

2.7 Examine your table. The Movie Name values should now all be in proper
case as desired.

Ken Sheridan
Stafford, England

 

[Guest]

Whoa! What you all posted seems waaaay over my head.
This is what I would like to accomplish:

If the movie title is:
One flew over the cuckoos nest
the result needs to be:
One Flew Over The Cuckoos Nest