report address formatting

[perryclisbee via AccessMonster.com]

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

=CanShrinkLines (StrConv([JobsiteAddressL ine1],3),StrConv([JobsiteAddressL ine2]

,3),Trim(StrCon v([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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200606/1

[salad]

Re: report address formatting

perryclisbee via AccessMonster.c om wrote:
[color=blue]
> Hi,
> I have the following on my report to format addresses:
>
> =CanShrinkLines (StrConv([JobsiteAddressL ine1],3),StrConv([JobsiteAddressL ine2]
>
> ,3),Trim(StrCon v([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
>[/color]
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,l en(strAddr1)-3) & _
Ucase(Right(str Addr1,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