StrConv Question

[Finedean]

Hi All,
I have a column Named AttendingProv like this

AttendingProv

N.S.U.H. @SYOSSET
JEWISH UNIVERSITY HOSP
N.S.U.H. @GLEN COVE
N.S.U.H. @PLAINVIEW
DALE UNIV HOSPITAL

I would like to convert the column (AttendingProv) to small cap, but I have a delima..

I did the following =strconv([AttendingProv],3) and I got the following:

N.s.u.h. @Syosset
Jewish University Hosp
N.s.u.h. @Glen Cove
etc

What I would like to do is get the following result:

N.S.U.H. @Syosset
Jewish University Hosp
N.S.U.H. @Glen Cove
etc.

Make all letters cap except N.S.U.H.
I hope this clear.
Thanks
Dean

 

[SgtJarrow]

Try:

IIf(Left([AttendingProv],8)="N.S.U.H.","N.S.U.H. @" & StrConv(Mid([AttendingProv],11),3),StrConv([AttendingProv],3))

But this REQUIRES that there will always be the format of "N.S.U.H. @"

If that will not always be the case, give some more info

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Finedean]

Thanks for the quick reply.
Yes, there is a different case and it spelled like this:

N.S.U.H. -MANHASSET AMBULANCE
N.S.U.H. -HOME HEALTH CARE

 

[SgtJarrow]

The problem I had with your original post was that when I used StrConv against "@SYOSSET", it still set the first S to lowercase...because it is not the first character in the string.

This should work, now as long as the first 8 characters to check are always "N.S.U.H."

IIf(Left([AttendingProv],8)="N.S.U.H.","N.S.U.H. " & Mid([AttendingProv],10,1) & StrConv(Mid([AttendingProv],11),3),StrConv([AttendingProv],3))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Finedean]

Beautiful. Thank you sooooooo much.