Make Text Proper Case (Access 2000)

Topic

New Reply

If someone types in all uppercase, is there a way to change it all to lowercase, except for the first letter, after it was typed in a field? I know you can go all uppercase or lowercase, but how about having the first letter of each word uppercase, then the rest lowercase

Thanks

Reply | Quote

Replies

Try the StrConv function – if you set the conversion type parameter to 3, it changes text to Proper Case.

Reply | Quote

Sweet !
Thanks

Reply | Quote

I found a great example for the event ‘On Key Press’ up on this forum some time ago and it is great. I use it in almost all my applications that have name and address input.
I put a function in the module and pass the text and the ascii code from the field to the function.
My code example:

Private Sub Address_KeyPress(keyASCII As Integer)
‘capitalize first letter
strString = Me![Address].Text
Call CapFirst(strString, keyASCII)
End Sub

Public Function CapFirst(mString As String, keyASCII As Integer)
‘cap first letter of each word in name and address
lngPos = Len(strString)
If lngPos = 0 Then
keyASCII = Asc(UCase(Chr$(keyASCII)))
ElseIf Mid$(strString, lngPos, 1) = Space$(1) Then
keyASCII = Asc(UCase(Chr$(keyASCII)))
End If
End Function

Reply | Quote

I tried your Sub and function and got a compile error “Variable Not Defined”. I just cut and pasted your code (changing the control name). What could I have done wrong. thanks

Reply | Quote

What line of code was highlighted?

Reply | Quote

strString =
of the line
strString = Me![FirstName].Text
is highlighted.

I haven’t worked in Access in a while, so I’m more than a little rusty. I do seem to remember a “Proper” function that was simple and may have been put in the Format field of a text box.

Thanks, for your help.

Reply | Quote

If you have Option Explicit set in your module (as we strongly recommend you do), then you need to declare the variable either at the module level or in the event procedure before you can use it.

There is no built-in ProperCase funtion, but there is a vbProperCase constant that can be used to code to pass the value 3 to the StrConv function. Unfortunately, you can’t use constants in queries. When you’re working there, you have to use the value represented by the constant.

Reply | Quote

Thanks for your reply, but most of that is gibberish to me. Isn’t “strString = Me![FirstName].Text ” the variable? I’m sorry, but I just don’t understand why something so basic (initial caps) is so complicated; just another example of Microsoft being able to very complex things, but not simple things (rant over).

If I wanted to use the strConv Function, I don’t understand what to do after I type “Public Function StrConv(string, conversion, LCID). I tried replacing the text in parens with the Control name and 3, but that didn’t work, so I’m obviously missing something; Microsoft help just doesn’t give any good examples of what to do with these things.

Thanks again.

Reply | Quote

No, strString is the variable and it has to be declared like this:

Dim strString as String

StrConv is built into VBA, it isn’t a function you create. Look it up in the object browser in the VB editor by opening any of the modules in the database and clicking on the object browser, then entering StrConv in the Find combo. Ignore the last argument as long as you aren’t trying to use it somewhere besides the current database. If the control name is MyControl, the syntax is StrConv([MyControl], vbProperCase), at least if you’re using it in a form. If you’re using it in a query, you have to use a 3 instead of vbProperCase because SQL (the query language) doesn’t understand VB constants or variables.

Itnitial caps seems simple, but in fact it isn’t that simple at all. Even word processors can have some trouble with it, and Access is a database, not a word processor.

Reply | Quote

No, strString is the variable and it has to be declared like this:

Dim strString as String

StrConv is built into VBA, it isn’t a function you create. Look it up in the object browser in the VB editor by opening any of the modules in the database and clicking on the object browser, then entering StrConv in the Find combo. Ignore the last argument as long as you aren’t trying to use it somewhere besides the current database. If the control name is MyControl, the syntax is StrConv([MyControl], vbProperCase), at least if you’re using it in a form. If you’re using it in a query, you have to use a 3 instead of vbProperCase because SQL (the query language) doesn’t understand VB constants or variables.

Itnitial caps seems simple, but in fact it isn’t that simple at all. Even word processors can have some trouble with it, and Access is a database, not a word processor.

Reply | Quote

Thanks for your reply, but most of that is gibberish to me. Isn’t “strString = Me![FirstName].Text ” the variable? I’m sorry, but I just don’t understand why something so basic (initial caps) is so complicated; just another example of Microsoft being able to very complex things, but not simple things (rant over).

If I wanted to use the strConv Function, I don’t understand what to do after I type “Public Function StrConv(string, conversion, LCID). I tried replacing the text in parens with the Control name and 3, but that didn’t work, so I’m obviously missing something; Microsoft help just doesn’t give any good examples of what to do with these things.

Thanks again.

Reply | Quote

If you have Option Explicit set in your module (as we strongly recommend you do), then you need to declare the variable either at the module level or in the event procedure before you can use it.

There is no built-in ProperCase funtion, but there is a vbProperCase constant that can be used to code to pass the value 3 to the StrConv function. Unfortunately, you can’t use constants in queries. When you’re working there, you have to use the value represented by the constant.

Reply | Quote

strString =
of the line
strString = Me![FirstName].Text
is highlighted.

I haven’t worked in Access in a while, so I’m more than a little rusty. I do seem to remember a “Proper” function that was simple and may have been put in the Format field of a text box.

Thanks, for your help.

Reply | Quote

What line of code was highlighted?

Reply | Quote

I tried your Sub and function and got a compile error “Variable Not Defined”. I just cut and pasted your code (changing the control name). What could I have done wrong. thanks

Reply | Quote

What your code does here is effectively what the StrConv function does.

I always go with the least amount of code as I can, that much less to debug.

Reply | Quote

What your code does here is effectively what the StrConv function does.

I always go with the least amount of code as I can, that much less to debug.

Reply | Quote

That code predates both the StrConv function and the Change event, I believe. It used to be the only way to capture keystrokes as they were entered. It isn’t necessary any more because it can be done more simply. The AfterUpdate event of the control can be used to run StrConv and will be less resource-intensive than KeyPress, which fires every time a key is depressed.

Reply | Quote

That code predates both the StrConv function and the Change event, I believe. It used to be the only way to capture keystrokes as they were entered. It isn’t necessary any more because it can be done more simply. The AfterUpdate event of the control can be used to run StrConv and will be less resource-intensive than KeyPress, which fires every time a key is depressed.

Reply | Quote

I found a great example for the event ‘On Key Press’ up on this forum some time ago and it is great. I use it in almost all my applications that have name and address input.
I put a function in the module and pass the text and the ascii code from the field to the function.
My code example:

Private Sub Address_KeyPress(keyASCII As Integer)
‘capitalize first letter
strString = Me![Address].Text
Call CapFirst(strString, keyASCII)
End Sub

Public Function CapFirst(mString As String, keyASCII As Integer)
‘cap first letter of each word in name and address
lngPos = Len(strString)
If lngPos = 0 Then
keyASCII = Asc(UCase(Chr$(keyASCII)))
ElseIf Mid$(strString, lngPos, 1) = Space$(1) Then
keyASCII = Asc(UCase(Chr$(keyASCII)))
End If
End Function

Reply | Quote

Sweet !
Thanks

Reply | Quote

I have use this code in an update query to change from Uppercase to Lowercase; but now I can’t find it. How would use this code.

Reply | Quote

To change a field MyField to UPPER CASE, you can set the ‘Update to’ line in the update query to

UCase([MyField])

or

StrConv([MyField],1)

To change to lower case, use

LCase([MyField])

or

StrConv([MyField],2)

To change to Proper Case, use

StrConv([MyField],3)

Reply | Quote

Try the StrConv function – if you set the conversion type parameter to 3, it changes text to Proper Case.

Reply | Quote