Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103171

From BetaArchive Wiki
Knowledge Base


ACC: Printing on Avery 5267 Labels

Article ID: 103171

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q103171

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you print Avery 5267 labels, the text may appear to creep down the page.

CAUSE

Avery 5267 labels are not cut in precise .5-inch increments on the page. Because the labels are slightly larger than .5 inch, if you print in .5- inch increments, the text appears to creep down the page.

RESOLUTION

When you place the controls in a report, allow room for the label discrepancy. The following steps print three lines of text on Avery 5267 labels. This example uses a function that concatenates the fields for optimal use of space on each label.

  1. Create a new report using the Label Wizard (or Mailing Label Wizard in version 2.0)
  2. Add only one field to your label.
  3. Open the report in Design view.
  4. In Microsoft Access 97:

    On the File menu, click Page Setup. Set the Top and Bottom margins to .5 inches, and set the Left and Right margins to .25 inches. Click the Columns tab, and set Numbers Of Columns to 4, Row Spacing to 0, and Column Spacing to 0.3 inches. Under Column Size, click to select the Same As Detail check box.

    In Microsoft Access 7.0:

    On the File menu, click Page Setup. Set the Top and Bottom margins to .5 inch, and set the Left and Right margins to .25 inch. Click the Layout tab, and set Items Across to 4, Row Spacing to 0, and Column Spacing to 0.3 inches. Under Item Size, click to select the Same As Detail check box.

    In Microsoft Access 1.x and 2.0:

    On the File menu click Print Setup. Set the Top and Bottom margins to .5 inch, and set the Left and Right margins to .25 inch. Click More and set Items Across to 4, Row Spacing to 0, and Column Spacing to 0.3 inches. Under Item Size, select the Same As Detail option.
  5. Make the width of the report 1.75 inches.
  6. Set the Height property for the Detail section to .5 inch and set the CanGrow and CanShrink properties to No.
  7. Locate the text box control and manually set the properties as follows:

          Control: Text Box
             Top: .01
             Height: .48
             CanGrow: Yes
             CanShrink: Yes
             Font: Arial
             FontSize: 8
             ControlSource:
              =AddressBlock([First],[Last],[Address],[City],[State],[Zip])
                            


    NOTE: In the ControlSource property use your field names, not [first], [last], and so on. Also the number of arguments used in the ControlSource property must match the number of arguments in the AddressBlock function.

  8. Create a new module, and type the following code:

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          Option Explicit
    
          Function AddressBlock$ (First, Last, Address, City, State, Zip)
             Dim A1$, A2$, A3$, CR$
    
             CR$ = Chr(13) & Chr(10)  'Carriage return and line feed
             A1$ = IIf(ISB(First),"",First & " " & IIf(ISB(Last),"" ,Last _
             & CR$))
             A2$ = IIf(ISB(Address),"",Address & CR$)
             A3$ = City & ", " & State & " " & Zip
             AddressBlock = A1$ & A2$ & A3$    'Concatenate the strings.
          End Function
    
          Function ISB (V) As Integer
             If IsNull(V) or V = "" Then ISB = True Else ISB = False
          End Function
                            

The spacing above creates a .01 reserve above and below the text control. Variations of this layout may also print correctly.

NOTE: The Mailing Label Report Wizard creates two controls, each with a height of .17, leaving a reserve below and above the text for label adjustments.

Keywords: kbprb kbusage KB103171