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

[pukhton]

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

[Jim Doherty]

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

[JConsulting]

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

[missinglinq]

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

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

[JConsulting]

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