Jerry Nesbett asks:
“Is there a function in VB6 that would convert a string date to a numeric date? In other words, convert the date 12/25/2002 to 20021225? I have searched the help, but I don’t know the correct word to put in the index.”
Jerry is told to “…have a look at the Format() function.”
Dim strDate As String Dim dtDate As Date dtDate = #12/25/2002# strDate = Format$(dtDate, "YYYYMMDD") MsgBox strDate
Jerry wrote back:
“Things are so simple sometimes. I was looking for something more complicated and wasn’t even thinking about that. Thanks for your help.”
See how easy it is to get answers to your questions in the DevX discussion groups?
Here’s an example:
Dim s As String Dim sProper As String s = "bill jones" sProper = StrConv(s, vbProperCase) Debug.Print sProper ' prints "Bill Jones"
(The “vbProperCase” in the preceding code is a built-in constant. This code is tested.)
Seems pretty straightforward. But doubt reared its ugly head when Rick Rothstein began to discuss the inevitable exceptions. He wrote:
“I’ve found that proper casing, while an attractive concept at first thought, becomes problematic when used ‘in the wild.’ For example, any reference to Microsoft’s MVP program would be converted to ‘Mvp.’ Sorry, that looks anemic at best. Names are always a problem. ‘Tom O’Reilly’ becomes ‘Tom O’reilly’ (I don’t think he would like that). Are you into math history? I pity any reference to ‘Pierre de Fermat’ (that ‘de’ starts with a small ‘d’) which becomes ‘Pierre De Fermat.’ Hopefully, you won’t have to handle any case sensitive passwords (such as ‘1A23bc4D56E’). And we can construct many, many more ‘exceptions.’ To properly use proper casing, you really have to know what will possibly be fed into the StrConv function so that you won’t be surprised later on.”
The problem lies in the fact that StrConv is a “dumb” function; it converts everything to proper (or rather, improper) case. For example, if you use StrConv on the term “.NET,” it converts it to “.net” (it converts the period and lower-cases the rest of the word).
Dim s As String Dim sProper As String s = ".NET" sProper = StrConv(s, vbProperCase) Debug.Print sProper ' prints ".net"
Furthermore, what if you don’t want words to be proper-cased? For example, at DevX, we use the convention “Web services,” not “Web Services.” Or think of article titles?what rules could you use to teach a computer to create properly-cased headlines? For example, the “The” in “The Best Way to Proper-Case Text in VB” is (properly) capitalized. But in the equivalent title “Improve the VB Proper-Case Function”, it’s not capitalized?capitalization depends on placement as well as the word itself.
This isn’t so much of a problem when dealing strictly with databases. Dean Earley posted code that he uses to run through his registration “database every few weeks to ‘correct’ all the lower/odd case names.” He says it catches most of them.
NName = StrConv(PName, vbProperCase) If InStr(NName, "Mc") > _0 Then Mid(NName, InStr(NName, "Mc") + 2, 1) = UCase(Mid(NName,InStr(NName, "Mc") + 2, 1)) If InStr(NName, "Mac") > _0 Then Mid(NName, InStr(NName, "Mac") + 3, 1) = UCase(Mid(NName,InStr(NName, "Mac") + 3, 1)) If InStr(NName, "O'") > _0 Then Mid(NName, InStr(NName, "O'") + 2, 1) = UCase(Mid(NName,InStr(NName, "O'") + 2, 1)) If InStr(NName, "-") > _0 Then Mid(NName, InStr(NName, "-") + 1, 1) = UCase(Mid(NName,InStr(NName, "-") + 1, 1)) If InStr(NName, "Van ") > _0 Then Mid(NName, InStr(NName, "Van "), 4) = "van " If InStr(NName, "Den ") > _0 Then Mid(NName, InStr(NName, "Den "), 4) = "den " If InStr(NName, "De ") > _0 Then Mid(NName, InStr(NName, "De "), 3) = "de "
This code may or may not catch all improperly cased names, as there are many exceptions.
Unfortunately, one problem with writing your own function is that you also lose the ability of the StrConv function to find word boundaries; in other words, having decided that StrConv() isn’t what you need, you’re on your own, and you’ll have to start by writing your own function to split text into separate words.
Simply put, proper-casing text is a relatively intractable problem. Language is complex, and you can’t get around that by using the StrConv function.