General discussion

Locked

Access - first letter capital

By jwes1980 ·
I need a way to make access force the first letters of each entry into a field as a cap and the rest as lowercase. I currently have many records all in caps and they need to be changed to just the first letter of each word being a capital. I have looked inside of access and tried a few different settings in a Text field and format setting but nothing so far has worked. What can I enter or do to correct this?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to Access - first letter cap ...

well, this is not very elegant, but what about this idea.
copy the structure only of the table with records that have fields that need their capitalization changed to a new table, name it say, fixcaps.
Change the Input Mask property of the fields that need punctuation fixed. Look up Input Mask in access help for examples. you probably want one like this: ?<
(that's question mark, less than symbol)
then you can do an append query to 'fixcaps' to make all letters but the first lowercase.

Collapse -

by sgt_shultz In reply to

change the input mask in the appropriate fields in the new empty copy table called 'fixcaps' then make an append query to append all the records in your old table to the new table. is what i was trying to say.
look up append query in Access help also...

Collapse -

by techrep In reply to Access - first letter cap ...

Here is a simple function that will convert the textbox string to lower case and then capitalize the first letter. Just call the function in the After_Update event for each textbox that needs it like this: =FirstCap()

Function FirstCap()
Dim FirstLetter, buf

buf = Screen.ActiveControl
If IsNull(buf) Then Exit Function

buf = LCase(buf) 'convert to lower case
'convert first letter to upper
FirstLetter = UCase(Mid(buf, 1, 1))
If Len(buf) > 1 Then 'concatenate the strings
buf = FirstLetter & Mid(buf, 2)
End If
Screen.ActiveControl = buf
End Function

You can put this function in the code behind the form or in a general module where it can be called from all forms.

Collapse -

by RichTee In reply to Access - first letter cap ...

An easy way to modify all the existing entries would be to run the following query. Substitute the name of your table and the name of your field in the appropriate place.

In addition, you can use this query for future input. You could work this query to run when the form the data is entered on is closed.

SELECT[tblNAME].[FIELD], STRCONV(EMAIL,3) as FIELD_in_Proper_Case FROM tblNAME;

Good Luck, Rich

Collapse -

by badermsb In reply to Access - first letter cap ...

This will work as long as all the fields in the table are text fields. If not you would need to check the field type.

Sub SetProperCase()

Dim db As Database
Dim rs As Recordset
Dim iFldCnt As Integer
Dim iCntr As Integer

Set db = CurrentDb

Set rs = db.OpenRecordset("Table Name", dbOpenTable)

If Not rs.BOF And Not rs.EOF Then

iFldCnt = rs.Fields.Count - 1

Do Until rs.EOF
For iCntr = 0 To iFldCnt
rs.Edit
rs.Fields(iCntr).Value = StrConv(rs.Fields(iCntr).Value, vbProperCase)
rs.Update
Next iCntr
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Collapse -

by badermsb In reply to Access - first letter cap ...

This will work as long as all the fields in the table are text fields. If not you would need to check the field type.

Sub SetProperCase()

Dim db As Database
Dim rs As Recordset
Dim iFldCnt As Integer
Dim iCntr As Integer

Set db = CurrentDb

Set rs = db.OpenRecordset("Table Name", dbOpenTable)

If Not rs.BOF And Not rs.EOF Then

iFldCnt = rs.Fields.Count - 1

Do Until rs.EOF
For iCntr = 0 To iFldCnt
rs.Edit
rs.Fields(iCntr).Value = StrConv(rs.Fields(iCntr).Value, vbProperCase)
rs.Update
Next iCntr
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Collapse -

by parier In reply to Access - first letter cap ...

Depending on version
and assuming this is a Lastname, Firstname

if last and first name are two fields:
=proper([last]) &", " & proper([first])

if first and last name are one field
=proper([first last])
Hope this helps.

Collapse -

by parier In reply to

Forgot to mention poper([field]) will also capitalize the first leter of each work, so '111 puter north road' will become '111 Puter North Road' and 'bob j vila' will become 'Bob J Vila'

If this is a permanent fix you wish to to, use cna crete a update query like this:
update table set firstlast = proper([firstlast]

This will permananetly update the field to proper (Capitalize each first letter of each word)

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums