Format to convert JOHN D. SMITH JR. to John D. Smith Jr.
- Thread starter DKO
- Start date
Sorry, I should have been more specific. Is there any way I can do the same thing as a format? If I want the names to be all caps in the source table and on forms, but want it to display as proper case on one report, Is there a way I can set the format of the field on the report to display in proper case...or modify the source query? I'd much rather do it on the report itself instead of the query - the query's already a little complicated because I have the name seperated into 4 fields (Different reports require the name to be in different formats and that was the easiest way for me to do it) and I have to concatenate several fields plus additional words to get the report to display properly.
Here's the query that the report's based on.
Here's the query that the report's based on.
Last edited:
Dennisk
AWF VIP
- Local time
- Today, 19:35
- Joined
- Jul 22, 2004
- Messages
- 1,649
you require a proper function that you can embed in the queries driving the reports.
here is one
here is one
Code:
Public Function Proper(strName) As String
' T-SQL by Jens-Michael Lehmann (SQL Server Central)
' Converts McDonalds, O'Brian's, MacPherson's and the Jens-Michael's of the world
' But names followed by 2 or more initials ie brown ag are converted to Brown Ag
' Converted to VBA by Dennis Keeling Sept. 2005
'
Dim intStringLength As Integer
Dim intLoopCounter As Integer
Dim intWordCounter As Integer
Dim intWordStart As Integer
Dim strOutputString As String
Dim strCharAtPos1 As String * 1
Dim strCharAtPos2 As String * 2
Dim strCharAtPos3 As String * 3
If IsNull(strName) Then
Exit Function
End If
'Initialize
strOutputString = ""
intStringLength = Len(strName)
intLoopCounter = 1
intWordStart = 1
intWordCounter = 1
Do While intLoopCounter <= intStringLength
strCharAtPos1 = LCase(Mid(strName, intLoopCounter, 1))
strCharAtPos2 = LCase(Mid(strName, intLoopCounter, 2))
strCharAtPos3 = LCase(Mid(strName, intLoopCounter, 3))
' If at start of word then upper case the character
If intWordStart = 1 Then
strCharAtPos1 = UCase(strCharAtPos1)
intWordStart = 0
intWordCounter = 0
End If
If intWordStart > 1 Then
intWordStart = intWordStart - 1
End If
' If we encounter McSomething, Start a word
If strCharAtPos2 = "Mc" And intWordCounter = 0 Then
intWordStart = 2
End If
' If we encounter MacSomething, Start a word
If strCharAtPos3 = "Mac" And intWordCounter = 0 Then
intWordStart = 3
End If
' If we encounter a hochkomatta (high comma(German))
If strCharAtPos1 = "'" Then
intWordStart = 1
End If
' If we encounter a hyphen
If strCharAtPos1 = "-" Then
intWordStart = 1
End If
' If we encounter a comma
If strCharAtPos1 = "," Then
intWordStart = 1
End If
' If we encounter an underscore
If strCharAtPos1 = "_" Then
intWordStart = 1
End If
' If we encounter a dot
If strCharAtPos1 = "." Then
intWordStart = 1
End If
' If we encounter a space
If strCharAtPos1 = " " Then
intWordStart = 1
End If
' Form the output string
strOutputString = strOutputString & strCharAtPos1
intLoopCounter = intLoopCounter + 1
intWordCounter = intWordCounter + 1
Loop
Proper = strOutputString
End FunctionThanks, but how would I go about imbedding that in an SQL query?
RG said:
Thanks, but when I tried that it in a simplified select query, it asked me for a parameter value for vbProperCase and didn't return the corrected format. When I tried it as the Union query, it asked me for the parameter value and gave an error that the "expression was typed incorrectly or is too complex to be evaluated..."
I couldn't find any syntax errors.
to use a function in a query just replace the column name like this
pCaseroper(yourSurname)
this function will handle all the mcs, macs etc.
or if you write sql Proper(yoursurname) as PCase
Works like a charm. Thanks.
