The PROPER function

[Guest]

Access2003 on Windows Xp Pro

In the past in Excel, I have used the PROPER function to covert names to a
first-letter-only capitalization format. The function works well, even
dealing appropriately with the likes of "Van Horn" or "O'Neill". Wanting to
apply the same approach with in an update query, I attempted to use it but
met with a unrecognized function. As a work around, I am using the following:

current format: [LastName] = JOHNSON

my approach:

Left([LastName],1) & LCase(Right([LastName],LEN([LastName])-1))

I am falling short on the double barrelled and hypenated family names. Any
tricks or suggestions out there? I am also wondering about a search for a
specific character within a string......such as is there a space " " or a
hypen "-" in this character string? As always assistance is greatly
appreciated.

 

[fredg]

Access2003 on Windows Xp Pro

In the past in Excel, I have used the PROPER function to covert names to a
first-letter-only capitalization format. The function works well, even
dealing appropriately with the likes of "Van Horn" or "O'Neill". Wanting to
apply the same approach with in an update query, I attempted to use it but
met with a unrecognized function. As a work around, I am using the following:

current format: [LastName] = JOHNSON

my approach:

Left([LastName],1) & LCase(Right([LastName],LEN([LastName])-1))

I am falling short on the double barrelled and hypenated family names. Any
tricks or suggestions out there? I am also wondering about a search for a
specific character within a string......such as is there a space " " or a
hypen "-" in this character string? As always assistance is greatly
appreciated.

Access uses the StrConv() function to capitalize the first letter of
every word:

To convert the text when entering it:
On the Form's control's AfterUpdate event code:
Me![ControlName] = StrConv([ControlName],3)

To convert the text in your table after entry:
Update YourTable Set YourTable.FieldName = StrConv([FieldName],3);

Note: this will incorrectly capitalize some words which contain more
than one capital, i.e. O'Brien, MacDonald, IBM, Jones-Smith, ABC,
etc., and incorrectly capitalize some words that should not have any
capitals, or whose capitaliztion depends upon usage, such as e. e.
cummings, abc, van den Steen.
Then some names can be capitalized in more than one manner, depending
upon personal preference, i.e. O'Connor and O'connor, McDaniels and
Mcdaniels, etc. are both correct.

You can create a table of exceptions and have the code use DLookUp
with a message if one of the exception words is found.