By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,884 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,884 IT Pros & Developers. It's quick & easy.

Upper case and Lower case

P: 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Initials_AfterUpdate()
  3.  
  4.     If IsNull(Screen.ActiveControl) = False Then
  5.  
  6.         Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
  7.  
  8.     End If
  9. End Sub
  10.  

any Idea?

Thanks again
Aug 30 '07 #1
Share this Question
Share on Google+
15 Replies


missinglinq
Expert 2.5K+
P: 3,532
You can do the same thing to data already existing by using the same function in an UpDate query.

Linq ;0)>
Aug 30 '07 #2

P: 75
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.
Aug 30 '07 #3

P: 75
I am not sure how to do that.
I got that thing to work, now my field is in all upper case

Expand|Select|Wrap|Line Numbers
  1.  
  2. UPDATE TABLENAME SET FIELDNAME = UCASE([FIELDNAME]);
  3.  
  4.  
but what If u want to convert something like that.

Linezolid instead of LINEZOLID

Thx.
Aug 31 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
Expand|Select|Wrap|Line Numbers
  1. strconv(YourFieldName,vbProperCase)
will capitalize the first letter of each word in a field.

Expand|Select|Wrap|Line Numbers
  1. 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)>
Aug 31 '07 #5

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Initials_AfterUpdate()
  3.  
  4.     If IsNull(Screen.ActiveControl) = False Then
  5.  
  6.         Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
  7.  
  8.     End If
  9. End Sub
  10.  

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

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_Surname_KeyPress
  2.     Dim Character
  3.     Character = Chr(KeyAscii)
  4.     KeyAscii = Asc(UCase(Character))
  5.  
  6. Exit_Surname_KeyPress:
  7.     Exit Sub
  8.  
  9. Err_Surname_KeyPress:
  10.     MsgBox err.Description, vbInformation, "Surname KeyPress Command Cancelled"
  11.     Resume Exit_Surname_KeyPress
Regards

Jim
Aug 31 '07 #6

P: 75
Expand|Select|Wrap|Line Numbers
  1. strconv(YourField,vbProperCase)
will capitalize the first letter of each word in a field.

Expand|Select|Wrap|Line Numbers
  1. 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?
Aug 31 '07 #7

Jim Doherty
Expert 100+
P: 897
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

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblcustomerinformation SET tblcustomerinformation.Lastname = UCase([LastName]);
Regards

Jim
Aug 31 '07 #8

P: 75
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 31 '07 #9

Jim Doherty
Expert 100+
P: 897
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
Aug 31 '07 #10

P: 75
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
Aug 31 '07 #11

Jim Doherty
Expert 100+
P: 897
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


Expand|Select|Wrap|Line Numbers
  1. Public Function Capitalize(str)
  2.     'This function capitalizes each of the words in a sentence.
  3.     Dim i
  4.     Dim t
  5.     t = LCase(CStr(str))
  6.             If t <> "" And Not IsNull(t) Then
  7.               t = UCase(Mid(t, 1, 1)) & Mid(t, i + 2)
  8.                   For i = 1 To Len(t) - 1
  9.                     If Mid(t, i, 1) = "." Then
  10.                          ' Capitalize words/letters preceded by "."
  11.                          t = Left(t, i) & UCase(Mid(t, i + 1, 1)) & Mid(t, i + 2)
  12.                     End If
  13.                     If Mid(t, i, 2) = Chr(13) + Chr(10) Then
  14.                          ' Capitalize words preceded by carriage return plus
  15.                          ' linefeed combination.
  16.                          t = Left(t, i) & UCase(Mid(t, i + 2, 1)) & Mid(t, i + 3)
  17.                     End If
  18.                     If Mid(t, i, 1) = " " Then
  19.                          ' Capitalize words preceded by a space:
  20.                          t = Left(t, i) & UCase(Mid(t, i + 1, 1)) & Mid(t, i + 2)
  21.                     End If
  22.                   Next
  23.           End If
  24.     Capitalize = t
  25. End Function
Aug 31 '07 #12

JConsulting
Expert 100+
P: 603
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
Sep 1 '07 #13

missinglinq
Expert 2.5K+
P: 3,532
Using the native Access function
Expand|Select|Wrap|Line Numbers
  1. strconv(YourFieldName,vbProperCase)
as stated in Post # 5 above, you can replace the 25 line custom function of Post # 12 with
Expand|Select|Wrap|Line Numbers
  1. Public Function Convert2ProperCase(YourField As String) As String
  2. Convert2ProperCase = StrConv(YourField, vbProperCase)
  3. End Function
In your query you would then use
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. StrConv([YourFieldName],3)
Both will capitalize the first letter of each word in the field, which is the OPís objective.

Linq ;0)>
Sep 1 '07 #14

missinglinq
Expert 2.5K+
P: 3,532
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)>
Sep 1 '07 #15

JConsulting
Expert 100+
P: 603
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
Sep 1 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.