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

[Sqygle]

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 could try in the after update of the control to convert it

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

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()

 

[theDBguy]

Hi. Welcome to AWF!

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

 

[GPGeorge]

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]

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]

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]

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]

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]

You could try in the after update of the control to convert it

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

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

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

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]

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.

 

[isladogs]

Also look at the Proofing and Autocorrect options:

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