Using Proper Function in a query

[cterjesen]

I am pulling data from a linked table in a query and I want to use something like the "Proper" function in Excel in this query because some of the data comes through in all caps and some comes through all lower case.

Does anyone know how to do this? I'm an intermediate user of Access, so I'm not familiar with a lot of VB programming. I was just wondering if there was a similar function available in Access.

 

[cterjesen]

Won't this change ever letter to Upper Case? I just want the first letter of every word to be upper case and all the other letters to be lower case.

 

[CT Witter]

I tried to edit my post but it disappeared?! So here is the new function.

Function Proper(X)
'  Capitalize first letter of every word in a field.
'  Use in an event procedure in AfterUpdate of control;
'  for example, [Last Name] = Proper([Last Name]).
'  Names such as O'Brien and Wilson-Smythe are properly capitalized,
'  but MacDonald is changed to Macdonald, and van Buren to Van Buren.
'  Note: For this function to work correctly, you must specify
'  Option Compare Database in the Declarations section of this module.
Dim Temp$, C$, OldC$, i As Integer
  If IsNull(X) Then
    Exit Function
  Else
    Temp$ = CStr(LCase(X))
    '  Initialize OldC$ to a single space because first
    '  letter needs to be capitalized but has no preceding letter.
    OldC$ = " "
    For i = 1 To Len(Temp$)
      C$ = Mid$(Temp$, i, 1)
      If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
        Mid$(Temp$, i, 1) = UCase$(C$)
      End If
      OldC$ = C$
    Next i
    Proper = Temp$
  End If
End Function

Now if you want to properly handle cases like MacDonald is changed to Macdonald you will need to build a table to lookup these values.

The table should be named "Names" and have one field titled "NewName". In this table you put the proper format ie MacDonald.

Here is the function.

Function ProperLookup(ByVal InText As Variant) As Variant
'
' Similar to Proper(), but uses a table (NAMES) to look up words that don't fit the
' general formula.
'
Dim OutText As String, Word As String, i As Integer, C As String
Dim db As DATABASE, T As Recordset
'
' Output Null and other non-text as is
'
  If VarType(InText) <> 8 Then
    ProperLookup = InText
  Else
    Set db = CurrentDb
    Set T = db.OpenRecordset("Names", dbOpenTable)
    T.Index = "PrimaryKey"
    OutText = ""
    Word = ""
    For i = 1 To Len(InText)
      C = Mid$(InText, i, 1)
      Select Case C
        Case "A" To "Z"        ' if text, then build word
          Word = Word & C
        Case Else
          If Word <> "" Then   ' if not, then append existing word and then the character
            T.Seek "=", Word
            If T.NoMatch Then
              Word = UCase(Left(Word, 1)) & LCase(Mid(Word, 2))
            Else
              Word = T!Name
            End If
            OutText = OutText & Word
            Word = ""
          End If
          OutText = OutText & C
      End Select
    Next i
'
' Process final word
'
    If Word <> "" Then
      T.Seek "=", Word
      If T.NoMatch Then
        Word = UCase(Left(Word, 1)) & LCase(Mid(Word, 2))
      Else
        Word = T!Name
      End If
      OutText = OutText & Word
    End If
'
' Close table and return resuslt
'
    T.Close
    db.Close
    ProperLookup = OutText
  End If
End Function

HTH,
CT

 

[giacomo]

Use the following function in your query...

StrConv([your_data_field],3)

 

[Norie]

Surely

StrConv(Strconv([your_data_field],2),3)

 

[giacomo]

Norie

StrConv doesn't require the string to be lower case first. I tried the following scenarios with acceptable results, is there one I am missing?

strConv("ALL CAPS",3) = "All Caps"
strConv("lower case",3) = "Lower Case"
strConv("mIxEd CaSe",3) = "Mixed Case"

Surely

StrConv(Strconv([your_data_field],2),3)

 

[Norie]

Yr right - misread definition

 

[cterjesen]

Thanks everyone! The strConv function worked beautifully!