format question

[Steve Goodrich]

I have a simply query, Two of the fields being First Name and Surname. All
the fields in the database are in upper case.

I would like to format the query so I get the Initial only of the first
name and lower case for the Surname with a capital first letter

So STEVE GOODRICH becomes S Goodrich

I have got so far with the following in an extra column

Initial: Left([First Name],1) & " " & [Surname]

This gives me S GOODRICH

I know how to format the whole field to upper or lower case using < for
lower and > for upper but how do I get
S Goodrich

Thanks for any advice

Steve

 

[Douglas J. Steele]

Try:

StrConv(Left([First Name],1) & " " & [Surname], 3)

Note, though, that you'll have problems with hyphenated names, or names like
McDonald or von Beethoven.

?StrConv("G SCOTT-HERON", 3)
G Scott-heron
?StrConv("R MCDONALD", 3)
R Mcdonald
?StrConv("L VON BEETHOVEN", 3)
L Von Beethoven

 

[tedmi]

In your expression, replace [Surname] with:
Left(Surname,1)&LCase(Mid(Surname,2,Len(Surname)-1)

 

[Keith Wilby]

I have a simply query, Two of the fields being First Name and Surname. All
the fields in the database are in upper case.

I would like to format the query so I get the Initial only of the first
name and lower case for the Surname with a capital first letter

So STEVE GOODRICH becomes S Goodrich

I have got so far with the following in an extra column

Initial: Left([First Name],1) & " " & [Surname]

This gives me S GOODRICH

I know how to format the whole field to upper or lower case using < for
lower and > for upper but how do I get
S Goodrich

Thanks for any advice

Steve

Use the StrConv function with the "proper case" argument.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

Use the StrConv function with the "proper case" argument.

Just to expand on that, create a module with the following function:

Public Function libProperCase(str As String) As String
libProperCase = StrConv(str, vbProperCase)
End Function

You can then call that function from a query:

Initial: Left([First Name],1) & " " & libProperCase([Surname])

Keith.

 

[Steve Goodrich]

Thank Guys,
Works perfectly