Adding Font Attributes to Concatenated Fields 5

[jdegeorge]

Hi:

I have a control on a report that's made up of several table fields:

=IIf(IsNull([Resolution]),"Description: " & [Description] & Chr(13) & Chr(10) & "Assessment: " & [Assessment],"Description: " & [Description] & Chr(13) & Chr(10) & "Assessment: " & [Assessment] & Chr(13) & Chr(10) & "Resolution: " & [Resolution])

It does what I need it to do, but I'd really love to make the words "Description", "Assessment" and "Resolution" bold if I could. Is there a way to do that?

Jim DeGeorge

 

[dhookom]

Have you tried anything to trouble-shoot this? My first step would be to change the border from transparent to a red or blue (different colors for different text boxes) and see which one is doing the pushing.

Duane
MS Access MVP

 

[jdegeorge]

Duane

I didn't even think of trouble shooting this. I'll try playing with the borders. Thanks for the idea.

Jim DeGeorge

 

[jdegeorge]

Duane:

I put different color borders around the boxes and what I found was interesting. In all cases, the boxes were never larger than they had to be. It's hard to draw this here, but I'll try:

Here's what one looked like:
[tt]___________________________
|Description: This is the |
|description of the item. |
----------------------------
____________________________
|Assessment: This is |
|the assessment of the item.|
----------------------------[/tt]

Then, later on a similar record would look like this:

Here's what one looked like:
[tt]___________________________
|Description: This is the |
|description of the item. |
----------------------------

____________________________
|Assessment: This is |
|the assessment of the item.|
----------------------------[/tt]

with the extra blank line inbetween. I thought that it might have been this situation:

Here's what one looked like:
[tt]___________________________
|Description: This is the |
|description of the item. |
| |
----------------------------
____________________________
|Assessment: This is |
|the assessment of the item.|
----------------------------[/tt]

But it wasn't.

Any ideas why the boxes would separate like this?

Jim DeGeorge

 

[Eupher]

Do you have any other boxes to the right of the description box that have the "Can Grow" property set to "yes"? If so, if the data in that box causes it to grow, all other controls that are placed lower in that section (even if they are not directly underneath!) will be forced downward as well. For example:

This...

 _________________________      _______________________
|Description: This is the |    |Another box over here  | 
|description of the item. |    |that can grow.         |
 -------------------------      -----------------------
 _________________________
|Assessment: This is the  |
|the assessment.          |
 -------------------------

...can become this if the the field to the right should grow...

 _________________________      _______________________
|Description: This is the |    |Another box over here  | 
|description of the item. |    |that can grow.  And now|
 -------------------------     |it has grown, forcing  |
                               |the field on the left  |
                               |below downward.        |
                                -----------------------
 _________________________
|Assessment: This is the  |
|the assessment.          |
 -------------------------

Ken S.

 

[Eupher]

p.s. Instead of "... any other boxes to the right..." I should have said "...any other boxes to the right or left..." I was making an assumption, and after re-reading my post it occurred to me I might have assumed wrongly. But you get the idea, I'm sure.

Ken S.

 

[jdegeorge]

Eupher

That's it exactly! Now, if you can fix it, there's a star in it for you (wink, wink)!

Jim DeGeorge

 

[Eupher]

Try putting your Description and Assessment fields inside a subreport, and set the Can Grow property of the subreport to "yes":

 _________________________      ________________________
|Subreport containing     |    |This field can grow, but|
|multiple fields, can grow|    |it won't affect the     |
|set to "yes"             |    |fields inside the       |
 -------------------------     |adjacent subreport      |
                                ------------------------

Give that a try...

Ken S.

 

[jdegeorge]

Ken

This sounds intriguing. The subreport would be based on the same query as the main report, with the same grouping and sorts, etc.? How would they be linked so that the correct fields appear in the right place?

Jim DeGeorge

 

[Eupher]

Hmmm... The only time I've used this procedure, there was no linking involved, as the report was designed to only retrieve a single record, and all the fields were populated via a call to a VBA function.

I don't know how your data is set up, but I'm guessing there is some kind of key field in your query that uniquely identifes each record, so that would probably be your link field. If you don't want to display it, you could just make it an invisible field.

Ken S.

 

[Eupher]

Okay, I just hacked together a couple of tables and a report to verify that it works. In my test I have 2 tables, "Employees" and "tblTestingGrow". The Employees table has fields [SSN] (the PK) , [LastName], [FirstName], [PayGrade]. All the fields are text. The tblTestingGrow table has [RecID] (the PK), [SSN] (the FK), [Description], [Assessment], and [Recommendation]. [RecID] is autonumber, [SSN], [Description], and [Assessment] are text, [Recommendation] is memo. Then I put some data in the tables.

I built a query and selected [LastName], [FirstName], [SSN], and [PayGrade] from the Employees table and [Description], [Assessment], and [Recommendation] from the tblTestingGrow table. There's an inner join on the SSN fields, of course. I named the query "qryTestingGrow".

Okay, my report is bound to qryTestingGrow, and I placed the [Recommendation] field toward the right side of the Detail section. I created a group/sort on [PayGrade] and put the [PayGrade] field in the group header. Then I built my subreport, bound it also to qryTestingGrow and put the [LastName], [FirstName], [SSN], [Description], and [Assessment] fields in the Detail section - NO sorting or grouping.

Then I went back to the main report and placed the subreport, linking on the [SSN] field. Worked perfectly, the [Recommendation] field can grow as much as needed without changing the spacing of the fields in the subreport.

HTH...

Ken S.

 

[jdegeorge]

Ken

Here's my SQL for the report:

[tt]SELECT tblArea.Area, tblGaps.Disposition, tblItemStatus.Item_Status, tblGaps.[Gap#], tblCategory.Category, tblGaps.GapID, tblGaps.Criticality, tblGaps.Description, tblGaps.DateOpened, tblGaps.Assessment, tblPassword.User, DLookUp("[User]","tblPassword","[UserID#] = " & [NFS_Owner1]) AS Owner1, DLookUp("[User]","tblPassword","[UserID#] = " & [NFS_Owner2]) AS Owner2, DLookUp("[User]","tblPassword","[UserID#] = " & [NFS_Owner3]) AS Owner3, DLookUp("[User]","tblPassword","[UserID#] = " & [NFS_Owner4]) AS Owner4, DLookUp("[Contact]","tblContact","[ContactID] = " & [Conversion_Owner]) AS ConversionOwner, [Status Date] & " - " & [Status] AS GapStatus, TempTable.DependencyList, tblGaps.Resolution, tblGaps.ExistingPolicy, tblGaps.NewPolicy, tblGaps.FTE, tblGaps.TechnicalConsiderations, tblArea_1.Area AS DecisionMaker, tblGaps.ImplementationDate, tblGaps.TargetFirm
FROM ((TempTable RIGHT JOIN ((tblPassword INNER JOIN ((tblGaps INNER JOIN tblArea ON tblGaps.[Area/Function] = tblArea.AreaID) INNER JOIN tblCategory ON tblGaps.Category = tblCategory.CategoryID) ON tblPassword.[UserID#] = tblGaps.NFS_Owner) LEFT JOIN qryMostRecentStatus ON tblGaps.GapID = qryMostRecentStatus.IncidentID) ON TempTable.Master = tblGaps.[Gap#]) INNER JOIN tblItemStatus ON tblGaps.Gap_Status = tblItemStatus.Item_StatusID) LEFT JOIN tblArea AS tblArea_1 ON tblGaps.PolicyGroup = tblArea_1.AreaID
WHERE (((tblGaps.Conversion)=[forms]![frmSignOn]![ctlConversion]))
ORDER BY tblArea.Area, tblGaps.Disposition, tblItemStatus.Item_Status DESC , tblGaps.[Gap#];[/tt]

As you can see, it involves 6 tables and 1 query that have MANY inner joins, etc. On top of this, there are 2 main group headers!

I'm giving you a star and a medal just for hanging in there with me on this one! Thanks!

Although I believe that your suggestion will keep the extra space from appearing, I don't believe that it would be worth the significant effort in my situation. It's a lot of work just to pretty this up. If I can't figure out something simpler, then I'll have to live with the extra space.

Jim DeGeorge

 

[Eupher]

Jim,

Yeah, I know what you mean. Sometimes the cost/benefit just doesn't balance out. For me there's a certain fear quotient, too. It's not rational, but I have a paranoia about tearing into a working database for fear that I'll pull on one string and the whole thing will unravel. Anyway, thanks for the star and the medal. Good luck with your project.

Ken S.

 

[Eupher]

Just another thought off the top of my head... I suppose in the Format event of your detail section, you could put in some code to dynamically re-position the Assessment field as needed. Let me tinker with it a little, it might work out to be a much more practical solution.

Ken S.

 

[jdegeorge]

You're determined on this one. I'm impressed. Thanks, but don't neglect your own work!

Jim DeGeorge

 

[MichaelRed]

You could also consider using the RTF Text box. While you still need to do the logic to decide wheather / where to have the font attributes, you do not need the seperate boxes.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jdegeorge]

Michael

I check Access help about RTF Text Boxes and could only find information about exporting the whole report to RTF. I'm using Access XP...is this a feature in this version?

Jim DeGeorge

 

[dhookom]

I don't generally recommend solutions that involve ActiveX controls since they need to be properly installed on all PCs using the application. Stephen Lebans has created (and provides free) RTF controls. Check his TOC at

http://www.lebans.com/toc.htm.

Duane
MS Access MVP

 

[jdegeorge]

Duane

I took a look at S. Lebans solution. It allows you to set font attributes for selected fields in a concatentated control, but I can't figure out if it'll let me set the font attributes for a free-form text label in such a control (i.e. ="Test" & [Field] & chr(13) & chr(10) & "Something" & [Field2]). I'd like to bold the TEST and SOMETHING labels not the field values.

Because I can't figure this out, and for the reason you mentioned about others needing to install versions of ActiveX, I'm not going to pursue this one. Thanks any way.

Jim DeGeorge

 

[MichaelRed]

RTF Text box is an MS supplied control in Ms. A. 2K and later.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Eupher]

Hmmm.... can't seem to find it. I'm running MS Access XP. Maybe it's just in the Developer Edition?

Ken S.