Field formatting

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

John

Hi

How can I achieve the following field formatting on a form at data entry?

1. Make first letter of each word in a field caps?

2. Make all letters in a field caps?

3. If there is an apostrophe in a word to make the letter following the
apostrophe a caps?

4. If there is a hyphen in a word to make the letter following the hyphen a
caps?

Many Thanks

Regards
 
Temporary change or permanent change. If you want to permanently change the
data do you need to change existing data (use an update query) or data as it
is entered into a form (use the control's after update event).

1) You can use the strConv function
StrConv([FieldName],3)
2) Use UCase or strConv
UCase([FieldName] or StrConv([FieldName],1)
3) and 4) are a bit unclear. Do you mean you want to capitalize the first
letter of each word and the next letter after those characters? If that is
correct, you will need a custom VBA function.

Try the following (NOT FULLY TESTED - use at your own risk). I wrote this long
ago and it is probably not the most efficient code.
'===========================================================
Public Function Title_Case(strChange As Variant, _
Optional strAddedSeparators As String) As Variant
'AUTHOR: John Spencer
'LAST MODIFIED: June 30, 1999
'DESCRIPTION: Changes characters in string to
'Uppercase the first letter of each word
'EXAMPLE: Title_Case("a Little red engine/that could")
' returns "A Little Red Engine/That Could"

Dim intCount As Integer
Dim strSeparator As String
strSeparator = " -&({[/:." & Chr(34) 'Uppercase character after
'one of these separator characters
'Chr 34 is double quote mark

Title_Case = strChange
If VarType(strChange) = vbString Then

If Len(strChange) > 0 Then
strSeparator = strSeparator & strAddedSeparators
strChange = UCase(Left(strChange, 1)) & _
LCase(Right(strChange, Len(strChange) - 1)) 'Do the first letter

For intCount = 2 To Len(strChange)
'UCase any letter that follows a space, dash, &, etc.
If InStr(strSeparator, Mid(strChange, intCount - 1, 1)) <> 0 Then
strChange = Left(strChange, intCount - 1) & _
UCase(Mid(strChange, intCount, 1)) & _
Mid(strChange, (intCount + 1))
End If
Next intCount

Title_Case = strChange
End If

Else
Title_Case = strChange

End If 'vartype is string

End Function
'========================================================================

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Hi

How can I achieve the following field formatting on a form at data entry?

1. Make first letter of each word in a field caps?
Click to expand...
2. Make all letters in a field caps?
Click to expand...

Use a format property of

Just a single greater-than character. It won't change what's stored but will
display it (in table datasheet view, you can use the same format in a textbox
on a form or report).
3. If there is an apostrophe in a word to make the letter following the
apostrophe a caps?

4. If there is a hyphen in a word to make the letter following the hyphen a
caps?
Click to expand...

The Format property is simply not capable of doing 1, 3 or 4. You'll need VBA
code as John suggests. The apostrophe bit is strange - do you want "bob's
diner" to show up as "Bob'S Diner"?
 
Back
Top