Format Mask

[Guest]

Hi How do I format the data in a table so the first letter is capital, but
there rest is not, eg Surnames? (> = all one format)

 

[AlCamp]

Timboo,
There's no pre-defined format definition for what you want.
There's > for All Caps and Proper([Some text Field]) where each
discrete "word" gets Capped. (This Is An Example)

You can store the data the way you want it, or... just "display" the data
the way you want it..."on the fly".

To store in your format, use the AfterUpdate event of each field...
[YourFieldName] = Ucase(Left([YourFieldName],1)) &
Mid([YourFieldName],2)

To just display the data, regardless of how it's stored, use the control
source for [txtYourFieldName]...
= Ucase(Left([YourFieldName],1)) & Mid([YourFieldName],2)
hth
Al Camp

 

[AlCamp]

Timboo,
I reconsidered my response. Everything's OK, but since you don't want
any capitals after the first character, then use this instead...
[YourFieldName] = Ucase(Left([YourFieldName],1)) &
LCase(Mid([YourFieldName],2))

so... "this IS aN ExamPle" would yield "This is an example"
hth
Al Camp

 

[John Vinson]

Hi How do I format the data in a table so the first letter is capital, but
there rest is not, eg Surnames? (> = all one format)

People named "van Slyke" or "de la Torre" or "MacDonald" may object if
you do this...

But there is no Format to do this. You can use VBA code in the
AfterUpdate event of a Form textbox to convert all lower-case user
input to Proper Case (Every Word Capitalized, Including Macdonald And
Van Slyke):

Private Sub txtSurname_AfterUpdate()
If StrComp(Me!txtSurname, LCase(Me!txtSurname), 0) = 0 Then
Me!txtSurname = StrConv(Me!txtSurname, vbProperCase)
End If
End Sub

The If clause ensures that if the user correctly types "MacDonald" or
"van Slyke", the program will leave it as entered.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

This works really great. Thanks Al.
--
Denys

Timboo,
I reconsidered my response. Everything's OK, but since you don't want
any capitals after the first character, then use this instead...
[YourFieldName] = Ucase(Left([YourFieldName],1)) &
LCase(Mid([YourFieldName],2))

so... "this IS aN ExamPle" would yield "This is an example"
hth
Al Camp

Hi How do I format the data in a table so the first letter is capital, but
there rest is not, eg Surnames? (> = all one format)

 

[SeeAll]

Hi,

If you wanted to enter forename and surname, or, forename, middlename and
surname how would you alter the mask?

eg 1.
[input] "james kirk" [data in table] "James KIRK"

eg 2.
[input] "james tiberius kirk" [data in table] "James Tiberius KIRK"

All names can be of variable length.

Regards

Jeff

 

[John Spencer]

Don't use one field for the name. Break it into three fields (or perhaps
more) - FirstName, MiddleName, LastName, Title, Suffix.
Also I would suggest storing the input as entered and then you can apply
formatting to the name when you want to use it. And if you wish you can
concatenate the parts together as needed. Storing the full name in one
field makes it very difficult to do a lot of operations (sort by last name,
display name in last name first name form, display the first name intial and
last name, return the person's title and last name).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SeeAll]

Thanks for your speedy reply John,

After I'd sent the query, I realised it would be easier to separate the data
into three fields, and, as you point out it would be far easier to do
searches on say, the surname field.

Regards

Jeff