Upper case and Lower case

[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

[missinglinq]

You can do the same thing to data already existing by using the same function in an UpDate query.

Linq ;0)>

[pukhton]

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.

[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.

[missinglinq]

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

[Jim Doherty]

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

[pukhton]

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?

[Jim Doherty]

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

[pukhton]

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.

[Jim Doherty]

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