report address formatting

[perryclisbee via AccessMonster.com]

Hi,
I have the following on my report to format addresses:

=CanShrinkLines(StrConv([JobsiteAddressLine1],3),StrConv([JobsiteAddressLine2]

,3),Trim(StrConv([JobsiteCity],3)) & ", " & UCase([JobSiteState]) & " " &
[JobsiteZip])
However, it does not figure out how to capitilize the part of the address
that has either SW, SE, NW, NE in it. Is there a function or some other way
to have the query sort this out? Currently the SW etc is being converted to
Sw etc.

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

[salad]

perryclisbee via AccessMonster.com wrote:

Hi,
I have the following on my report to format addresses:

=CanShrinkLines(StrConv([JobsiteAddressLine1],3),StrConv([JobsiteAddressLine2]

,3),Trim(StrConv([JobsiteCity],3)) & ", " & UCase([JobSiteState]) & " " &
[JobsiteZip])
However, it does not figure out how to capitilize the part of the address
that has either SW, SE, NW, NE in it. Is there a function or some other way
to have the query sort this out? Currently the SW etc is being converted to
Sw etc.

Thanks,

Perry

I don't know what CanShrinkLines is. Is it a function?
If it is, you might want to pass the JobSite and CSZ data to format it
intead. There you'd strConv Addr1/2. Then check if the last 3 chars
are SW/ SE/ NW/ NE. If so get the left part prior to the last 3 chars
and Ucase the remaining
Select Case Right(strAddr1,3)
Case " SW", " SE", " NW", " NE"
strAddr1 = Left(strAddr1,len(strAddr1)-3) & _
Ucase(Right(strAddr1,3))
End Select

[djfenske]

I ran into this myself. I used the "CanShrinkLines" function to format a report over a year ago. Since then, all my important Access objects were migrated into a new database file with all tables in a SQL Server back end. When I tried to run this report I got the same symptoms you described. In reviewing how I had set this up last year, I finally realized that the "CanShrinkLines" function was not built in to Access, but was a custom VBA function I had copied from a Microsoft web site into a module in the file. After I located the module and recreated it in the new file I was fine. Here's the URL for the microsoft site with the details: http://office.microsoft.com/en-us/as...494721033.aspx