Upper and Lower case conversion issues

[perryclisbee via AccessMonster.com]

Hi,

I have a report (rptCurrentJobList) whose select query (qryCurrentJobList) is
being populated with data from a linked table, where all data is capitalized.
As a result, the report address information is all capitalized and is grouped
in the following structure:

=CanShrinkLines([JobsiteAddressLine1],[JobsiteAddressLine2],Trim([JobsiteCity]
) & ", " & [JobSiteState] & " " & [JobsiteZip])

This setup allowed the info to be compressed into the required cell. I have
tried to use the StrConv ([fieldname],3) idea to format the data, but the
CanShrinkLines gets in the way. Is there a way to use both StrConv and
CanShrinkLines at the same time? I am not allowed to convert the format of
the linked tables directly.

Thanks,

Perry

 

[perryclisbee via AccessMonster.com]

Hi,

I tried the following, and it seems to work to a point:

=CanShrinkLines(StrConv([JobsiteAddressLine1],3),StrConv([JobsiteAddressLine2]
,3),Trim(StrConv([JobsiteCity],3)) & ", " & UCase([JobSiteState]) & " " &
[JobsiteZip])

The only problem I have now is when the address has a SW or NW etc in it.
The coding converts it to Se or Nw etc.. Any ideas?

Perry

Hi,

I have a report (rptCurrentJobList) whose select query (qryCurrentJobList) is
being populated with data from a linked table, where all data is capitalized.
As a result, the report address information is all capitalized and is grouped
in the following structure:

=CanShrinkLines([JobsiteAddressLine1],[JobsiteAddressLine2],Trim([JobsiteCity]
) & ", " & [JobSiteState] & " " & [JobsiteZip])

This setup allowed the info to be compressed into the required cell. I have
tried to use the StrConv ([fieldname],3) idea to format the data, but the
CanShrinkLines gets in the way. Is there a way to use both StrConv and
CanShrinkLines at the same time? I am not allowed to convert the format of
the linked tables directly.

Thanks,

Perry

 

[Larry Linson]

You can create a user-defined function to do whatever manipulations you want
to the data, but figuring out the possible combinations and just what should
and should not be capitalized is going to be the big problem. The
combinations you cite, NW and SW, would be special cases in such a UDP.

That problem is going to be compounded if you have addresses from multiple
countries in the DB.

Larry Linson
Microsoft Access MVP

Hi,

I tried the following, and it seems to work to a point:

=CanShrinkLines(StrConv([JobsiteAddressLine1],3),StrConv([JobsiteAddressLine2]
,3),Trim(StrConv([JobsiteCity],3)) & ", " & UCase([JobSiteState]) & " " &
[JobsiteZip])

The only problem I have now is when the address has a SW or NW etc in it.
The coding converts it to Se or Nw etc.. Any ideas?

Perry

Hi,

I have a report (rptCurrentJobList) whose select query (qryCurrentJobList)
is
being populated with data from a linked table, where all data is
capitalized.
As a result, the report address information is all capitalized and is
grouped
in the following structure:

=CanShrinkLines([JobsiteAddressLine1],[JobsiteAddressLine2],Trim([JobsiteCity]
) & ", " & [JobSiteState] & " " & [JobsiteZip])

This setup allowed the info to be compressed into the required cell. I
have
tried to use the StrConv ([fieldname],3) idea to format the data, but the
CanShrinkLines gets in the way. Is there a way to use both StrConv and
CanShrinkLines at the same time? I am not allowed to convert the format of
the linked tables directly.

Thanks,

Perry