Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Upper and Lower case conversion issues

Discussion in 'Information Technology' started by perryclisbee via AccessMonster.com, Jul 28, 2006.

  1. perryclisbee via AccessMonster.com

    Guest

    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

    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200606/1
     
  2. Loading...


  3. perryclisbee via AccessMonster.com

    Guest

    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

    perryclisbee wrote:
    >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


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200606/1
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    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

    "perryclisbee via AccessMonster.com" <u9916@uwe> wrote in message
    news:622a033d06050@uwe...
    > 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
    >
    > perryclisbee wrote:
    >>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

    >
    > --
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200606/1
     

Share This Page