help with Foematting uppercase

[RhinoBull]

Thanks. Trouble is that the input can be any city and is stored in access database.

So input is random and need to force the input to display capital letter after the space.

That's no trouble... As Martin stated just replace hard coded "new york" (which was done only for demonstration as most of our samples) with some variable you might have.
Or you may even wrapp it in a function:

Public Function ConvertToProperCase(ByVal strValue As String) As String
    ConvertToProperCase = StrConv(strValue, vbProperCase)
End Function
 
'usage:
Private Sub Command1_Click()
Dim sOldValue$, sNewValue$
 
    sOldValue = "new york"
    sNewValue = ConvertToProperCase(sOldValue)
    Debug.Print sNewValue
 
End Function