Control = StrConv([Control], 3)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I tell the macro to not format a word if it is already capitalized?

This macro works create if I enter a simple street name, however, if I want
and enter, say US Hwy 32, it will format it as Us Hwy 32. Would this maybe
require an if then statement or something?
 
Scotts said:
How can I tell the macro to not format a word if it is already
capitalized?

This macro works create if I enter a simple street name, however, if
I want and enter, say US Hwy 32, it will format it as Us Hwy 32.
Would this maybe require an if then statement or something?
Click to expand...

About the best you can do is check whether the control's value is in
mixed case or not. If it's all in upper case, or all in lower case,
convert it to proper case; if not, leave it alone. Code to do that,
which would most likely be set to run in the control's AfterUpdate
event, might look like this:

'----- start of example code -----
Private Sub txtMyTextbox_AfterUpdate()

With Me!txtMyTextbox

If StrComp(.Value, UCase(.Value), vbBinaryCompare) <> 0 Then
' it's not all upper case; is it all lower case?
If StrComp(.Value, LCase(.Value), vbBinaryCompare) <> 0 Then
' it's not all lower case, either, so leave it alone.
Exit Sub
End If
End If

.Value = StrConv(.Value, vbProperCase)

End Sub
'----- end of example code -----

Hmm, thinking about this, you could -- if you wanted to go to that much
trouble -- break the string up into individual words, and do this on a
word by word basis. This may be what you want, but it's a bit more
complicated.
 
How can I tell the macro to not format a word if it is already capitalized?

This macro works create if I enter a simple street name, however, if I want
and enter, say US Hwy 32, it will format it as Us Hwy 32. Would this maybe
require an if then statement or something?
Click to expand...

It's doing what it's supposed to do.

What you can do is create a table of exceptions, and DLookUp each word
in the field to see if it is one of the exceptions. Requires a bit of
coding in the Address control's AfterUpdate event.
It would also require you to continuously update the table as new
multi-capital words are run across.
If you need help with this, post back.
 
Thank you both for your input.

Fred- I think a list of exceptions would be best, I can only see a handful
of these...I look forward to your coding.
 
Thank you both for your input.

Fred- I think a list of exceptions would be best, I can only see a handful
of these...I look forward to your coding.
Click to expand...

Add a new table to your database.
Add 2 fields.
1 [ID] Autonumber Indexed No duplicates
2 [ExceptName] Text Datatype 255 chrs
Name the table 'tblExceptions'

Copy and Paste the following code into a Module:

*** Watch out for word wrap on the longer lines. ***

Function ConvExceptionsInField(StrIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
' In a multi-word string
On Error GoTo Err_Handler

Dim strWord As String
Dim intX As Integer
Dim intY As Integer
Dim strNewString As String
Dim intResponse As Integer
Dim strFind As String

StrIn = StrIn & " "

intX = InStr(1, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY + 1, intX - 1), vbProperCase)

Do While intX <> 0

If DCount("*", "tblExceptions", "[ExceptName]= " & Chr(34) & strWord &
Chr(34) & "") > 0 Then

strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
" & Chr(34) & strWord & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,
"Exception found!")

If intResponse = vbYes Then
strWord = strFind
End If
End If
strNewString = strNewString & strWord & " "
intY = intX + 1
intX = InStr(intY, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY, intX - intY), vbProperCase)

Loop

ConvExceptionsInField = strNewString
Exit_this_Function:
Exit Function
Err_Handler:
Resume Next
End Function

===============

Then code the AfterUpdate event of the control (named Address in this
example) used for address entry on your form:

If Not IsNull([Address]) Then
[Address] = ConvExceptionsInField([Address])
End If

Then enter the exception names into the table as you come across them,
i.e. McDonald, US, IBM, O'Connor, etc.

As you enter addresses, if a word is found in the table, yow will get
a message asking whether to retain the entered capitalization or
change it. As you find a new words, add them to the table.
 
I will give it a shot and post back...thanks in advance!

-----------------
Scott



fredg said:
Thank you both for your input.

Fred- I think a list of exceptions would be best, I can only see a handful
of these...I look forward to your coding.
Click to expand...

Add a new table to your database.
Add 2 fields.
1 [ID] Autonumber Indexed No duplicates
2 [ExceptName] Text Datatype 255 chrs
Name the table 'tblExceptions'

Copy and Paste the following code into a Module:

*** Watch out for word wrap on the longer lines. ***

Function ConvExceptionsInField(StrIn As String) As String

' Will find exceptions to Proper Case capitalization of names.
' In a multi-word string
On Error GoTo Err_Handler

Dim strWord As String
Dim intX As Integer
Dim intY As Integer
Dim strNewString As String
Dim intResponse As Integer
Dim strFind As String

StrIn = StrIn & " "

intX = InStr(1, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY + 1, intX - 1), vbProperCase)

Do While intX <> 0

If DCount("*", "tblExceptions", "[ExceptName]= " & Chr(34) & strWord &
Chr(34) & "") > 0 Then

strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
" & Chr(34) & strWord & Chr(34) & "")

intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,
"Exception found!")

If intResponse = vbYes Then
strWord = strFind
End If
End If
strNewString = strNewString & strWord & " "
intY = intX + 1
intX = InStr(intY, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY, intX - intY), vbProperCase)

Loop

ConvExceptionsInField = strNewString
Exit_this_Function:
Exit Function
Err_Handler:
Resume Next
End Function

===============

Then code the AfterUpdate event of the control (named Address in this
example) used for address entry on your form:

If Not IsNull([Address]) Then
[Address] = ConvExceptionsInField([Address])
End If

Then enter the exception names into the table as you come across them,
i.e. McDonald, US, IBM, O'Connor, etc.

As you enter addresses, if a word is found in the table, yow will get
a message asking whether to retain the entered capitalization or
change it. As you find a new words, add them to the table.
Click to expand...
 
Back
Top