Upper case and Lower case | Member | | Join Date: Jun 2007
Posts: 75
| |
Hi fellows
Just a quick question about my database. I have one table in my database which i import it from some other Access db. This table has many fields, such as Initials, Medication name etc....but they are all in different upper case and lower case? Is there a way in Access or VB code so we could change all the current data (in the fields) same Lower case or upper case.
I have this under text box control, and its working fine, but this will effect only on or for new entry on the form. It doesnt effect on the current data in the fields. -
-
Private Sub Initials_AfterUpdate()
-
-
If IsNull(Screen.ActiveControl) = False Then
-
-
Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
-
-
End If
-
End Sub
-
any Idea?
Thanks again
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,000
| | | re: Upper case and Lower case
You can do the same thing to data already existing by using the same function in an UpDate query.
Linq ;0)> | | Member | | Join Date: Jun 2007
Posts: 75
| | | re: Upper case and Lower case
Quote:
Originally Posted by missinglinq You can do the same thing to data already existing by using the same function in an UpDate query.
Linq ;0)> I am not sure how to do that.
| | Member | | Join Date: Jun 2007
Posts: 75
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton I am not sure how to do that. I got that thing to work, now my field is in all upper case -
-
UPDATE TABLENAME SET FIELDNAME = UCASE([FIELDNAME]);
-
-
but what If u want to convert something like that.
Linezolid instead of LINEZOLID
Thx.
| | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,000
| | | re: Upper case and Lower case
- strconv(YourFieldName,vbProperCase)
will capitalize the first letter of each word in a field. - strconv(left(YourFieldName,1),vbUpperCase)& right(YourFieldName,len(YourFieldName)-1)
will capitalize only the first letter of the first word in the field.
Linq ;0)> |  | Moderator | | Join Date: Aug 2007 Location: Derbyshire,England
Posts: 639
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton Hi fellows
Just a quick question about my database. I have one table in my database which i import it from some other Access db. This table has many fields, such as Initials, Medication name etc....but they are all in different upper case and lower case? Is there a way in Access or VB code so we could change all the current data (in the fields) same Lower case or upper case.
I have this under text box control, and its working fine, but this will effect only on or for new entry on the form. It doesnt effect on the current data in the fields.
-
-
Private Sub Initials_AfterUpdate()
-
-
If IsNull(Screen.ActiveControl) = False Then
-
-
Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
-
-
End If
-
End Sub
-
any Idea?
Thanks again
Here is one for you to consider it acts on the ON KEYPRSS event of the control so in other words it forces characters to upper case as you type them for your any new records you might put in there - On Error GoTo Err_Surname_KeyPress
-
Dim Character
-
Character = Chr(KeyAscii)
-
KeyAscii = Asc(UCase(Character))
-
-
Exit_Surname_KeyPress:
-
Exit Sub
-
-
Err_Surname_KeyPress:
-
MsgBox err.Description, vbInformation, "Surname KeyPress Command Cancelled"
-
Resume Exit_Surname_KeyPress
Regards
Jim
| | Member | | Join Date: Jun 2007
Posts: 75
| | | re: Upper case and Lower case
Quote:
Originally Posted by missinglinq - strconv(YourField,vbProperCase)
will capitalize the first letter of each word in a field. - strconv(left(YourField,1),vbUpperCase)& right(YourField,len(YourField)-1)
will capitalize only the first letter of the first word in the field.
Linq ;0)> Thank you for posting, but this will take care of new entry into the field. How would I do it for exsisting record in the table?
| | Moderator | | Join Date: Aug 2007 Location: Derbyshire,England
Posts: 639
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton Thank you for posting, but this will take care of new entry into the field. How would I do it for exsisting record in the table?
run an UPDATE query... this is some sample SQL that you can paste into the query SQL window and run as is provided you have a table of the same name and field of the same name and some data to work with - UPDATE tblcustomerinformation SET tblcustomerinformation.Lastname = UCase([LastName]);
Regards
Jim
| | Member | | Join Date: Jun 2007
Posts: 75
| | | re: Upper case and Lower case
Quote:
Originally Posted by Jim Doherty run an UPDATE query... this is some sample SQL that you can paste into the query SQL window and run as is provided you have a table of the same name and field of the same name and some data to work with
- UPDATE tblcustomerinformation SET tblcustomerinformation.Lastname = UCase([LastName]);
Regards
Jim I have this already, and all the record are in Ucase. but I want to run a query where I want my data is to be in this format.
"Some Name" currently I have all the information in Ucase.
| | Moderator | | Join Date: Aug 2007 Location: Derbyshire,England
Posts: 639
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton I have this already, and all the record are in Ucase. but I want to run a query where I want my data is to be in this format.
"Some Name" currently I have all the information in Ucase. Are you saying the first letter of EACH word in the same field should be capilized for all of your existing data please clarify
| | Member | | Join Date: Jun 2007
Posts: 75
| | | re: Upper case and Lower case
Quote:
Originally Posted by Jim Doherty Are you saying the first letter of EACH word in the same field should be capilized for all of your existing data please clarify yes u got it right.
Thx alot
| | Moderator | | Join Date: Aug 2007 Location: Derbyshire,England
Posts: 639
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton yes u got it right.
Thx alot This will capitalize the first letter of each word. Paste it into a module and save the module whatever you like bas_strings something like that. It is a Function that you can call from your query qrid like this
Capitalize([TheNameOfYourField])
The above line if entered into 'Update To' row of an UPDATE query in the grid will update your data . Remember nothing is perfect..what sequence of characters constitute a word as we know it? beware of anomolies
If you want to see the results before you update (ie: you are not sure) then use it as an extra column in a SELECT query by typing into the Fields row in the grid this:
MyCheck: Capitalize([TheNameOfYourField])
and then run the query as a SELECT query you will then see the differences side by side in datasheet - Public Function Capitalize(str)
-
'This function capitalizes each of the words in a sentence.
-
Dim i
-
Dim t
-
t = LCase(CStr(str))
-
If t <> "" And Not IsNull(t) Then
-
t = UCase(Mid(t, 1, 1)) & Mid(t, i + 2)
-
For i = 1 To Len(t) - 1
-
If Mid(t, i, 1) = "." Then
-
' Capitalize words/letters preceded by "."
-
t = Left(t, i) & UCase(Mid(t, i + 1, 1)) & Mid(t, i + 2)
-
End If
-
If Mid(t, i, 2) = Chr(13) + Chr(10) Then
-
' Capitalize words preceded by carriage return plus
-
' linefeed combination.
-
t = Left(t, i) & UCase(Mid(t, i + 2, 1)) & Mid(t, i + 3)
-
End If
-
If Mid(t, i, 1) = " " Then
-
' Capitalize words preceded by a space:
-
t = Left(t, i) & UCase(Mid(t, i + 1, 1)) & Mid(t, i + 2)
-
End If
-
Next
-
End If
-
Capitalize = t
-
End Function
|  | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: Upper case and Lower case
Quote:
Originally Posted by pukhton yes u got it right.
Thx alot There's been a lot of great work on this thread....but I have to ask. Why do you care what your data in your tables looks like Caps Wise? Access stores data for the sake of content, not looks. That's what forms and reports are for. And you have total control on what the data looks like using the format option. I have to wonder about justifying the time it takes to do this, when in reality, it doesn't matter. Just my .02
J
| | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,000
| | | re: Upper case and Lower case
Using the native Access function - strconv(YourFieldName,vbProperCase)
as stated in Post # 5 above, you can replace the 25 line custom function of Post # 12 with - Public Function Convert2ProperCase(YourField As String) As String
-
Convert2ProperCase = StrConv(YourField, vbProperCase)
-
End Function
In your query you would then use - Convert2ProperCase([YourFieldName])
Or by simply replacing vbUpperCase with its numeric equivalent of 3 (you can't use the VB constant vbUpperCase in a query) you could simply use - StrConv([YourFieldName],3)
Both will capitalize the first letter of each word in the field, which is the OP’s objective.
Linq ;0)> | | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,000
| | | re: Upper case and Lower case
Vis a vis Jconsulting's comments, I think it comes down to would you like to do the formatting once, at the source (read Table) or do it everytime you use the field? If the field appears in 3 forms and 3 reports, you'd have to format it 6 times!
Linq ;0)> | | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: Upper case and Lower case
Quote:
Originally Posted by missinglinq Vis a vis Jconsulting's comments, I think it comes down to would you like to do the formatting once, at the source (read Table) or do it everytime you use the field? If the field appears in 3 forms and 3 reports, you'd have to format it 6 times!
Linq ;0)> >>Linq and All,
That's a good point, and I concede that the option to format text should be controlled once, but through a function that can be re-used. So whether you're entering data directly, or using an update query or import spec, or outputting to a report or displaying in a form, the proper fields can be "filtered" as you go, so there's no after-the-fact cleanup. Taking that into account, the functions included in this thread are well written extremely useful for that purpose. I have added them to my Library! :o)
J
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
