If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Upper and Lower case conversion issues
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 structu =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/For...ccess/200606/1 |
#2
|
|||
|
|||
Upper and Lower case conversion issues
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 structu =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/For...ccess/200606/1 |
#3
|
|||
|
|||
Upper and Lower case conversion issues
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 structu =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/For...ccess/200606/1 |
Thread Tools | |
Display Modes | |
|
|