CREATING A "PROPER" TEXT FIELD IN ACCESS DATABASE (2019) (1 Viewer)

Sqygle

New member
Local time
Today, 12:38
Joined
Jan 24, 2024
Messages
3
I should be very pleased if someone could kindly tell me how I assign a text field in Access which I wish to have “Proper” text in. I hasten to point out that am a new user of Access 2019 (started this week) and whilst I can understand how to create various types of fields I have yet to find out how to achieve this.

I know that I could create a report to change data in fields to upper, lower and proper text, but wish to avoid this method, if it is possible. The database that I want to create will have several fields where "Proper” text is required. If there is an easy way of creating” Proper” fields in Access Database I should be very pleased learn how. An idiots guide would be greatly appreciated!

If I haven’t used the correct terminology, please accept my apologies, as I said, I am a new user.

Many thanks in advance.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:38
Joined
May 21, 2018
Messages
8,417
You could try in the after update of the control to convert it
Code:
Private Sub Title_AfterUpdate()
  Me.Title = StrConv(Me.Title, vbProperCase)
End Sub
if you want to do this for a lot of controls then make a procedure in a standard module
Code:
Public Function ConvertCase()
  Screen.ActiveControl.Value = StrConv(Screen.ActiveControl.Value, vbProperCase)
End Function

then you can select any control and in the after update type
=ConverCase()
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:38
Joined
Oct 29, 2018
Messages
21,288
Hi. Welcome to AWF!

What is stored in the tables doesn't have to be what is displayed to the user.
 

GPGeorge

Grover Park George
Local time
Today, 04:38
Joined
Nov 25, 2004
Messages
1,724
I should be very pleased if someone could kindly tell me how I assign a text field in Access which I wish to have “Proper” text in. I hasten to point out that am a new user of Access 2019 (started this week) and whilst I can understand how to create various types of fields I have yet to find out how to achieve this.

I know that I could create a report to change data in fields to upper, lower and proper text, but wish to avoid this method, if it is possible. The database that I want to create will have several fields where "Proper” text is required. If there is an easy way of creating” Proper” fields in Access Database I should be very pleased learn how. An idiots guide would be greatly appreciated!

If I haven’t used the correct terminology, please accept my apologies, as I said, I am a new user.

Many thanks in advance.
One key point is that "Proper" refers only to format for display; it does not refer to the datatype nor to the actual values:

"Blue", "blue" and "BLUE" all mean the same thing; only the formatting is different.

You cannot, therefore, "create" a Proper field. You can only format the text in that field in a specific way.

You can apply the Proper case formatting anywhere you need to display that format.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:38
Joined
May 21, 2018
Messages
8,417
One key point is that "Proper" refers only to format for display; it does not refer to the datatype nor to the actual value
That is not true. The conversion to proper case as demonstrated definitely changes the "actual value".
 

GPGeorge

Grover Park George
Local time
Today, 04:38
Joined
Nov 25, 2004
Messages
1,724
That is not true. The conversion to proper case as demonstrated definitely changes the "actual value".
Correct. I was trying to emphasize that the word itself does not change. However, if you do a binary comparison, they are different.

The point remains that this is not a setting on the field itself.

Thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:38
Joined
Feb 19, 2002
Messages
42,724
Welcome to our world:)

The strConv() function with the vbProperCase argument is good but not perfect. All it does is to capitalize the first letter of each word so it may not even do what you want. Generally we don't mush a person's name into one field. We split it out into at least two, sometimes 5 fields so we can easily manipulate the data for various uses. It can't be used to cap the first word of a sentence in a mass of text for example.

So, what type of data are you trying to proper case?

Here are two examples of how it works:

print strconv("the dog is running",vbProperCase)
The Dog Is Running

print strconv("o'brien",vbProperCase)
O'brian

If your language uses a lot of names like O'Brian or McDonald, you need to write your own function to handle them.
 

Sqygle

New member
Local time
Today, 12:38
Joined
Jan 24, 2024
Messages
3
Welcome to our world:)

The strConv() function with the vbProperCase argument is good but not perfect. All it does is to capitalize the first letter of each word so it may not even do what you want. Generally we don't mush a person's name into one field. We split it out into at least two, sometimes 5 fields so we can easily manipulate the data for various uses. It can't be used to cap the first word of a sentence in a mass of text for example.

So, what type of data are you trying to proper case?

Here are two examples of how it works:

print strconv("the dog is running",vbProperCase)
The Dog Is Running

print strconv("o'brien",vbProperCase)
O'brian

If your language uses a lot of names like O'Brian or McDonald, you need to write your own function to handle them.
Thanks for your response Pat. The first example is what I want to achieve i.e. If someone enters "123 the high street" the in the text field(s) it will change to "123 The High Street"
I will experiment with this over the weekend.
 

Sqygle

New member
Local time
Today, 12:38
Joined
Jan 24, 2024
Messages
3
You could try in the after update of the control to convert it
Code:
Private Sub Title_AfterUpdate()
  Me.Title = StrConv(Me.Title, vbProperCase)
End Sub
if you want to do this for a lot of controls then make a procedure in a standard module
Code:
Public Function ConvertCase()
  Screen.ActiveControl.Value = StrConv(Screen.ActiveControl.Value, vbProperCase)
End Function

then you can select any control and in the after update type
=ConverCase()
Thanks for you response Maj I shall experiment over the weekend. I used DFataease for many years, where I could define fields
You could try in the after update of the control to convert it
Code:
Private Sub Title_AfterUpdate()
  Me.Title = StrConv(Me.Title, vbProperCase)
End Sub
if you want to do this for a lot of controls then make a procedure in a standard module
Code:
Public Function ConvertCase()
  Screen.ActiveControl.Value = StrConv(Screen.ActiveControl.Value, vbProperCase)
End Function

then you can select any control and in the after update type
=ConverCase()
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:38
Joined
May 21, 2018
Messages
8,417
Here is a couple ideas if you were going to do this for many fields.
You might not want to do this in the after update if it is possible the user does not want the results returned. So instead of automatically changing in the afterupdate you would get a message to accept, cancel, or modify the suggestion. In this case it capitalizes "Of" which I would want lower case. So I can change it to lower case or simply cancel. I changed the case to Upper case in the region field.


WOO.png
 

Attachments

  • FixCase.accdb
    2.3 MB · Views: 27

isladogs

MVP / VIP
Local time
Today, 12:38
Joined
Jan 14, 2017
Messages
18,129
Also look at the Proofing and Autocorrect options:

1706191278903.png


For example, you may prefer to disable 'Correct TWo INitial CApitals'
 

Users who are viewing this thread

Top Bottom