Force First Letter of every word to UPPERCASE (no StrConv)

[yohansetiawan]

Hello,



I am very new to VBA in Access, and I have question.

How do I force first letter of every word on fields/spesificField to uppercase?
I know function called StrConv and the value is ProperCase to make that,
but that function is to make "McDonald" text to "Mcdonald" instead of "McDonald".
I only need forcing the first letter only to uppercase, and ignore the rest after first letter.
So when I type "mcDonald mcD" it converts to "McDonald McD" instead of "Mcdonald Mcd".

thanks.

[June7]

That would involve a complex custom user defined VBA coded function to accomplish. Or somehow customizing the spell checker dictionary. Why is this correction necessary? Maybe need to consider use of a combobox that restricts entry to items in list.

[yohansetiawan]

yes it would necessary for my database though, but when you say its complex then maybe I'll look alternative way.
maybe the procedure will be detect first input then after the space charater it will reset the procedure and continue itself all the way till end of input?
I'm wondering if this issue can be archive by someone
btw thanks for ur reply June7.

[June7]

Yes, your pseudocode logic is what code would do. Use string manipulation functions. Split function could parse the string to a one dimension array and then loop code would read through the array elements and reconstruct the string with capitalization. Something like:

Function Capital(strWords As String)
Dim aryWords As Variant
Dim strCapWords As String, i As Integer
aryWords = Split(strWords, " ")
For i = 0 To UBound(aryWords)
    strCapWords = strCapWords & " " & UCase(Left(aryWords(i), 1)) & Mid(aryWords(i), 2)
Next
Capital = strCapWords
End Function

[redbull]

I know this is not the solution... I use this to make every letter uppercase in a certain txtbox. Might be a step in the right direction.

Private Sub txtAccession_KeyPress(KeyAscii As Integer)
  Select Case KeyAscii
  Case Asc("a") To Asc("z")
  KeyAscii = KeyAscii + Asc("A") - Asc("a")
  End Select
  End Sub

Maybe with some trimming it could work?

[yohansetiawan]

Thanks to you guys for the replies.
I found this StrConv ProperCase built-in function code and maybe with some modification it would be nice

Function StrConvProperCase(sInp)
       Dim nPos
       Dim nSpc

       nPos = 1
       Do While InStr(nPos, sInp, " ", 1) <> 0
               nSpc = InStr(nPos, sInp, " ", 1)
               StrConvProperCase = StrConvProperCase & UCase(Mid(sInp, nPos, 1))
               StrConvProperCase = StrConvProperCase & LCase(Mid(sInp, nPos + 1, nSpc - nPos))
               nPos = nSpc + 1
       Loop

       StrConvProperCase = StrConvProperCase & UCase(Mid(sInp, nPos, 1))
       StrConvProperCase = StrConvProperCase & LCase(Mid(sInp, nPos + 1))
       StrConvProperCase = StrConvProperCase
End Function

[June7]

A variation on my suggestion. Have you tried any of the suggestions offered?

I just noticed a bug in my suggestion. It will place a space at beginning of string. Need to trim that:

Capital = Trim(strCapWords)

[yohansetiawan]

I already saved your function on public module, but I still confused how to use it.
where should I place your function in the event on my textField properties?

[June7]

Depends. Do you want to permanently change the data? Then use in the UpdateTo row of an UPDATE query. If you just want to have data display with this structure but not be changed, use the function in a constructed field of query or in ControlSource of a textbox or in AfterUpdate event code for textbox to correct the user input and save the correction to the field.

The strWords argument would be the fieldname, like:

=Capital([fieldname here])

[yohansetiawan]

An error occurred when I run update query with UpdateTo value is Capital([myField])
my update query is

UPDATE myTable SET myTable.myField = Capital([myField]);

the error says "undefined function 'Capital' in expression"

I already saved your function in module:

Public Function Capital(strWords As String)
    Dim aryWords As Variant
    Dim strCapWords As String, i As Integer
    aryWords = Split(strWords, " ")
    For i = 0 To UBound(aryWords)
    strCapWords = strCapWords & " " & UCase(Left(aryWords(i), 1)) & Mid(aryWords(i), 2)
    Next
    Capital = strCapWords
    Capital = Trim(strCapWords)
End Function

[June7]

Can't reproduce your error. If you want to provide project, will look at it.

The field name is actually myField?

I tested the function and it runs but I am encountering a different error. Null values in field causing grief. Revised function.

Public Function Capital(varWords As Variant) As Variant
    Dim aryWords As Variant
    Dim varCapWords As Variant, i As Integer
    varCapWords = Null
    If Not IsNull(varWords) Then
        aryWords = Split(varWords, " ")
        For i = 0 To UBound(aryWords)
        varCapWords = varCapWords & " " & UCase(Left(aryWords(i), 1)) & Mid(aryWords(i), 2)
        Next
    End If
    Capital = Trim(varCapWords)
End Function

[yohansetiawan]

no, I just creating new database for testing out this function, so I called it myField though LOL.
my actual database is too big to upload, could you upload your testing database with this function and query attached so I can look at it?
because I encounter same error when testing your revised function (again doh!)

so here I uploaded my testing database that produce same error.
the error says "Undefined function 'Capital' in expression."
Am I doing it wrong? please have a look at it.
many thanks for helping me so far.

[June7]

Problem is the code module and function have the same name. Change the code module name.

[yohansetiawan]

wow.. it works very nice!!!
much much thanks to you June7, thanks for your time and help!!