Custom Formats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bibby - 27 May 2005 19:58 GMT

Hi

Can anyone help me create a custom format for address fields - I would
like to be able to enter the address and it automatically change to
Sentence Case - easy for one word but can't seem to find the answer if
there are two or three words - such as Apple Tree Road

Thanks

Bib

Signature

bibby

Reply to this Message

Rick Brandt - 27 May 2005 22:54 GMT

> Hi
>
[quoted text clipped - 6 lines]
>
> Bib

Thats' not sentence case, that's proper case and the strConv function can do
that for you.

strConv("apple tree road", vbProperCase) = "Apple Tree Road"

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

John Vinson - 28 May 2005 02:49 GMT

>Hi
>
>Can anyone help me create a custom format for address fields - I would
>like to be able to enter the address and it automatically change to
>Sentence Case - easy for one word but can't seem to find the answer if
>there are two or three words - such as Apple Tree Road

A Format proprety isn't capable of doing this task; you need VBA code.
Note that Sentence Case will be WRONG for some addresses: I've got a
friend who lives on McCormick Lane for example!

Try putting the following code in the AfterUpdate event of a textbox
on the Form you're using to update this table. (Yes, you need a form;
no, table datasheets don't have any usable events):

Private Sub txtAddress_AfterUpdate()
' Only process entries which are all in lower case
If StrComp(Me!txtAddress, LCase(Me!txtAddress), vbBinary) = 0 Then
Me!txtAddress = StrConv(Me!txtAddress, vbProperCase)
End If
End Sub

This will convert "apple tree road" to "Apple Tree Road", but will
leave "van Slyke Avenue" and "MAC Street" and "McClintock Lane" alone
since they're already in mixed case.

John W. Vinson[MVP]

Reply to this Message