I have data that is captured by a third party from a web page - name, address, etc. They send it to us on a nightly basis. I use this info to create mailing labels for individuals who do not use an email address. The issue is the fact that the user may enter their info in all caps, mixed case, etc. In order to create nice looking labels, I would like to format the name in proper case. Is there a format command of some sort I can use when I do the query or when I create the report?
Here an SQL query using Lcase and Ucase:
ptext is the TextFile
tblbtext is the table
SELECT LCase([ptext]) AS Expr1,
UCase([ptext]) AS Expr2,
UCase(Left([ptext],1)) & LCase(Right([ptext],Len([ptext])-1))
The first part select the text in LowerCase, the second select the test in UpperCase, and the third will select the text in LowerCase except the first character that will be in UpperCase. I'm pretty sure there is an SQL function for that third part, but I can't remember, so until someone post it, that will do the job.
You'll have to create a function that use the following code:
'Someone has really badly written that
Dim str As String
str = "ft. WaYNE, LAS VEGAs"
'Let's show that oddity
'Split all word from the string
Dim arr() As String
arr = Split(str)
'Change all word
For x = 0 To UBound(arr)
arr(x) = UCase$(Left$(arr(x), 1)) & LCase$(Mid$(arr(x), 2))
'Unsplit the words
str = Join$(arr, " ")
'Show the well formatted string