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

[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([TheNameOfYourFi eld])

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([TheNameOfYourFi eld])

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