June 25, 2009 at 8:20 am
Hello! I have a report that is formatting and concatenating a number of address fields for mailing labels. Everything looks fine except for instances of numbered streets such as 21st Street or 3rd Avenue. I'm using strConv(Fields!StreetName.Value,3) to proper case this field (it's stored in all caps), which causes numbered streets to render as 21St or 3Rd.
I'm not a VBA expert by any means, so I'm hoping that someone knows of a catch-all function I can use to deal with numbered streets rather than having to write code to handle each individual exception.
Thanks in advance!
June 29, 2009 at 10:04 am
Just in case anyone cares, I ended up finding a T-SQL function to proper case
my fields before they're rendered in my report. I used the function created by
David Wiseman at http://www.wisesoft.co.uk/scripts/tsql_proper_case_udf.aspx and just added the following line to the "known exceptions" area:
when @Word LIKE '[0-9]%' AND NOT(@Word LIKE '%st' OR @Word LIKE
'%nd' OR @Word LIKE '%rd' OR @Word LIKE '%th') then UPPER(@Word)
This makes values like "9E" or "4A" capitalized, while values like "11th" or "22nd" stay lowercased.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply