Home  |  About  |   Search   

What's New
Table Of Contents
10 Commandments 

RunCommand Constants

Terms of Use


Reports: Variable Grow Box Report

Thomas M. Brittell

This is how it works.

blanks: StringBLine(StringCnt([Text],Chr(13))+(Int((Len([Text])/46)+0.5))+1)

 Download Access 97 sample database

    We have to decide which box will control the height of the row to print. Most of the time it is a memo field with some description or comment.

    In the query is a calculation to count the number of lines needed. This is done through two routines and one rule. The rule is that the font you use for printing the detail line must be fixed such as Courier or Courier New.

    The first routine is StringCnt (strIn, strFind). The routine takes the input ‘strIn’ and counts the number of occurrences found of the specified string provided in’strFind’. In this example you will see ‘Chr(13)’ which is a carriage return. Now if all line had real carriage returns on them we would be done. Unfortunately most of the time they do not. Auto word wrap is the most common display method which means at this point you might have 1 carriage return and 20 lines. To allow for this you take the total length of the text governing the height of the boxes divide it by the number of characters you will display in one line. In the example I used 46. To round off the line count I added 0.5 to the count. I then added 1 line to allow a margin and fudge factor on the bottom of each box. The second routine is StringBLine (intCnt) which generates a blank line based on the count provided. This gives me a blank set of data to put in each needed box forcing its size to what is needed. Minimize the number of boxes by strategically placing them between needed areas where you can use both sides for the appearance of 2 lines such as column 2 in the example. Please note that the memo box does not have a border itself. Instead a box is put behind the total detail line to frame everything.

    Now for the considerations. Yes this is crude, But for a large number of rows its fairly fast. You can work on the calculation and get very fancy. In one case I had to count the Capital letters, spaces and line feeds including each in the results. It took forever to run. This is why I now build these little examples for such problems and run test data. Page overflow of large blocks of text will require further refinement of the calculation.

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer