StrConv String Conversion 2

[jonperks]

Hi all, I've read all about strconv and the input masks, I use ASP and SQL normally but have to do something which should be easy in Access. I haven't used access and i pretty much have no idea. I have a whole table with tons of data and its all other the place in terms of format. I simply want to make all the data in the table Propercase.

Cheers in advance

 

[SteveR77]

For the table in question you can create a query that will use the Proper function that is listed below.

Basically for the fields in your query that you wish to be proper case in the query grid you would simply have

Proper([MyNameField])


The function follows -


Function Proper (var As Variant) As Variant
' Purpose: Convert the case of var so that the first letter of each word capitalized.

Dim strV As String, intChar As Integer, i As Integer
Dim fWasSpace As Integer 'Flag: was previous char a space?

If IsNull(var) Then Exit Function
strV = var
fWasSpace = True 'Initialize to capitalize first letter.
For i = 1 To Len(strV)
intChar = Asc(Mid$(strV, i, 1))
Select Case intChar
Case 65 To 90 ' A to Z
If Not fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar Or &H20)
Case 97 To 122 ' a to z
If fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar And &HDF)
End Select
fWasSpace = (intChar = 32)
Next
Proper = strV
End Function

 

[jonperks]

Hi sorry to be a pain, but i don't know how to do that in access. will that actualyl go into my table and update it?

 

[jonperks]

no problems now, found the SQL view , easy just type
StrConv(

.[Column],3) AS Table_column
works a treat.

 

[BobStubbs]

This code example might be useful to you ... you can use it to make fields on a form correct automatically to Proper Case when the field is updated. My function also takes care of names such as McDonald, O'Neill and Smith-Jones ...

1. Put this function in your Access global module.

Function ProperCase(stOneLine As String) As String

'----------------------------------------------------------
'- This function will convert a string to Proper Case - -
'- the initial letter of each word is capitalised. -
'- It will also handle special names such as O', Mc and -
'- hyphenated names -
'----------------------------------------------------------

Dim I As Integer
Dim bChangeFlag As Boolean
Dim stResult As String

'----------------------------------------------------------
'- No characters in string - nothing to do -
'----------------------------------------------------------
If Len(stOneLine) = 0 Then
ProperCase = ""
Exit Function
End If

'----------------------------------------------------------
'- Always set first letter to upper case -
'----------------------------------------------------------
stResult = UCase$(Left$(stOneLine, 1))

'----------------------------------------------------------
'- Now look at the rest of the string -
'----------------------------------------------------------
For I = 2 To Len(stOneLine)

'----------------------------------------------------------
'- If the previous letter triggered a capital, change -
'- this letter to upper case -
'----------------------------------------------------------
If bChangeFlag = True Then
stResult = stResult & UCase$(Mid$(stOneLine, I, 1))
bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case -
'----------------------------------------------------------
Else
stResult = stResult & LCase$(Mid$(stOneLine, I, 1))
End If

'----------------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen found -
'----------------------------------------------------------
Select Case Mid$(stOneLine, I, 1)
Case " ", "'", "-"
bChangeFlag = True
Case Else
bChangeFlag = False
End Select

Next I

'----------------------------------------------------------
'- Special handling for Mc at start of a name -
'----------------------------------------------------------
If Left$(stResult, 2) = "Mc" Then
Mid$(stResult, 3, 1) = UCase$(Mid$(stResult, 3, 1))
End If

I = InStr(stResult, " Mc&quot
If I > 0 Then
Mid$(stResult, I + 3, 1) = UCase$(Mid$(stResult, I + 3, 1))
End If

ProperCase = stResult

End Function

2. Add an 'After Update' Event Procedure to each field which is to be 'proper cased'. Example for an EmployeeSurname field:

Private Sub EmployeeSurname_AfterUpdate()
If Me.EmployeeSurname > "" Then
Me.EmployeeSurname = ProperCase(Me.EmployeeSurname)
DoEvents
End If
End Sub


That's it! When anything is typed into the field, it will be converted to proper case when the cursor exits from the field, e.g.

FRED > Fred
smith > Smith
mcdonald > McDonald
o'NEILL > O'Neill
SMITH-JONES > Smith-Jones