Create mailing labels in Access

Microsoft Office Access 2007 gives you several different options for creating labels containing data stored in your Access tables. The simplest is to use the Label Wizard in Access to create and print your labels from a report you create. In addition, you can import data into Access from other sources, such as Microsoft Office Excel 2007 workbooks and Microsoft Office Outlook 2007 contact lists and "merge" that data or existing Access tables with a Microsoft Office Word 2007 document and then create and print labels from Word.

What do you want to do?


Use Access to create and print labels

In Access, you create labels as a report that is formatted with a small page size to fit the desired label. The most common use of labels is for mailing, but any Access data can be printed in a label format for a variety of purposes. In the case of mailing labels, the report gets the address data from the tables or queries containing the addresses. Printing the report gives you a single label for each address from the underlying record source.

 Note   The steps in this article assume you have already created an Access database which contains tables (or linked tables) that contain the data you want on your labels. Find a link to more information about getting started with Access in the See Also section.

The Label Wizard asks you a series of questions about your labels and the data you want to display on them. The wizard creates the report, which you can then customize, if needed.

Create labels by using the Label Wizard in Access

  1. In the Navigation Pane, select or open the table or query which will be the record source for your labels.
  2. On the Create tab, in the Reports group, click LabelsButton image.

Access starts the Label Wizard.

First page of label wizard

  1. If you know the manufacturer and product number of the labels:
    • Choose the manufacturer in the Filter by manufacturer box.
    • Select your label in the What label size would you like? box.

Otherwise, try to find a product whose values in the Dimensions and Number across columns match your labels.

If you can't find a similar label:    

  • Click Customize and then click New.

The New Label dialog box is displayed.

New Label page of label wizard

  • Measure your labels and enter the measurements in the appropriate boxes in the New Label dialog box.
  • Enter a name for the custom labels in the Label Name text box, choose the label type and orientation you want, and then click OK.

If you have already created a custom label and you want to select it:    

  • Click Show custom label sizes, and then click the custom label you want.
  1. Select Sheet feed or Continuous under Label Type and then click Next.
  2. Select the font name, size, weight and text color you want on your label, and click Next.
  3. On the What would you like on your mailing label page of the wizard, choose the fields you want on your labels from the Available fields list, and double-click them to add to the Prototype label box.

Adding fields to a label in the label wizard

  • To add space between the fields in the Prototype label box, press SPACEBAR.
  • To create a new line, press ENTER.
  • To move between fields and lines, use the arrow keys.
  • To remove a field from the box, press BACKSPACE.
  • To add other text or punctuation (for example, a comma after the city name), click to place the cursor where you want the text to appear and begin typing. This text will be the same on each label.

 Note   You can only add fields of the following data types by using the Label Wizard: Text, Memo, Number, Date/Time, Currency, Yes/No, or Attachment. To add fields of the OLEObject or Hyperlink data types, open the label report in Design view after you have completed the Label Wizard, and then use the Field List pane to add the fields. To open the Field List pane while the report is open in Design view, press ALT+F8.

When you have the fields arranged on your label the way you want them, click Next.

  1. On the next page, you can choose fields on which to sort your labels. For example, you may want to sort by postal code for a large mailing. Choose the fields you want to sort by from the Available fields list, and double-click them to add to the Sort by box. When you are finished, click Next.

Setting the sort order in the label wizard

  1. On the last page, enter a name for your labels, and click Finish.

Last page of the label wizard

If you chose See the labels as they will look printed on the last page of the Label Wizard, Access will display the labels in Print Preview. You can click on the label to zoom in and out, or use the zoom control on the status bar.

Zoom control

If you chose Modify the label design on the last page of the Label Wizard, Access displays the label report in Design view.

Customize the appearance of the label

If the resulting labels don't look the way you want, you can customize the layout by opening the report in Design view and making your changes. When working with labels, it is usually easier to use Design view where you have more precise control over the layout. To see the results of changes you make in Design view, switch to Print Preview.

If your data is not lining up with the labels correctly

In many cases, you can fix minor problems with your label layout by making adjustments in Design view. You can move the controls and you can adjust the label size by changing the width or height of the Detail section.

To switch to Design view, right-click the report in the Navigation Pane and then click Design ViewButton image on the shortcut menu.

If you need to change margins or spacing between labels, you can use the Page Setup dialog box while in Design view.

On the Page Setup tab, in the Page Layout group, click ColumnsButton image. The Page Setup dialog box is displayed with the Columns tab selected.

Columns tab of Page Setup dialog box

You can change the following settings in the three sections of the Page Setup dialog box:

Section Option Description
Grid Settings Number of Columns This should equal the number of labels across the page.
  Row Spacing Enter a new value to increase or decrease the vertical space between the rows of labels.
  Column Spacing Enter a new value to increase or decrease the horizontal space between the columns of labels.
Column Size Width

Keep in mind the margin settings and column spacing when setting this value.

You can check and adjust the margins by clicking Print Options in the Page Setup dialog box.

  Height Adjust the height of each label, as needed.
  Same as Detail When selected, sets the column width and height equal to the width and height of the Detail section in Design view.
Column Layout Down, then Across When selected, your label data prints down the first column, then down the second column, and so on.
  Across, then Down When selected, your label data prints across the first row, then across the second row, and so on.

 Note   You can also use the Page Setup dialog box while in Print Preview. When you click OK to close the dialog box, you can see how your new settings affect the labels.

You may need to experiment to arrive at the optimal alignment of your data on the labels, especially for multiple-column sheet-fed labels. A label report consists of a number of space-occupying areas. For example, there are the labels themselves, the space between the labels, and the space occupied by the page margins. So if your sheet of labels is 8.5 inches wide and you use a 0.25 inch margin on the left and on the right, there are only 8 inches of available space (8.5 minus 0.5). If you design your report for two columns of labels and you use 0.25 inches for the space between labels (the column spacing setting in the Page Setup dialog box), you now have only 7.75 inches available for the labels themselves. Thus, the width of the Detail section must not be greater than 3.875 inches (7.75 / 2 columns) in order for the label data to fit properly. The same principle applies in the vertical dimension— you must consider the top and bottom margins and the row spacing when adjusting the height of your labels.

If the settings in the Page Setup dialog box and/or the width of the controls in the report design result in some information not fitting on the page when printed or previewed, Access displays the "Some data may not be displayed" message. This is a sure sign that you need to fine-tune your page settings and perhaps adjust the size of the controls in your report design. If you see this message, try either of the following:

  • Reduce the width of the controls in the Detail section so that they fit within the calculated space available for a single label. See the example earlier in this column to learn how to calculate the space available.
  • Reduce the size of the page margins in the Page Setup dialog box. This will increase the space available for the information on your labels.

Print the labels

  1. Open the report in any view (or select the report in the Navigation Pane).
  2. Click the Microsoft Office Button Button image, and then click Print.Button image

Access displays the Print dialog box.

  1. Enter your choices for options such as printer, print range, and number of copies.
  2. Click OK.

Top of Page Top of Page

Learn tips and tricks for creating labels

  • Select the addresses that you want to print    If you do not want to print all of the addresses in the underlying record source, do one of the following:
    • Select addresses by specifying one or more criteria    Create a parameter query and specify this query as the label report's record source. When you open the report, the query prompts you to specify the criteria for retrieving records. The records that match your criteria are then retrieved and made available to the label report.
      If you are using Word to create and print the addresses, specify the parameter query as the data source during the mail merge operation.
    • Select addresses by browsing the address records     If you want to browse your address list and pick the ones you want to print labels for, do the following:
      1. In Design view, add a Yes/No field named PrintLabel to the underlying address table. When you switch to Datasheet view, in the PrintLabel column, you will see a check box in each row.
      2. Use the Simple Query Wizard to create a query that includes all of the address fields and the PrintLabel field.
      3. Open the query in Design view, and in the Criteria cell of the PrintLabel field, type Yes. The query will retrieve only those addresses that you want to print.
      4. Set the RecordSource property of the label report to the name of the query.
      5. Before printing labels, open the table in Datasheet view and select the check boxes for those addresses that you want to print. Clear the check boxes corresponding to the addresses that you don't want to print. Then, open the report and print it.
  • Print addresses directly onto envelopes    You might want to print addresses directly onto envelopes instead of printing them onto labels and then applying the printed labels to the envelopes. To print a label on an envelope, you need to define a custom label. In step 1 of the wizard, when you are prompted to specify the label details, click Customize and follow the instructions in step 4. Remember to set Label Type to Sheet Feed.

Top of Page Top of Page

Print Access data by using the Mail Merge Wizard in Word

If your addresses are stored in Access, but you want to export them to Word so that you can use its mail merge functionality to format and print your labels, you can use the Mail Merge Wizard in Word.

  1. In the Navigation Pane, select the table or query that contains the data you want to print.
  2. On the External Data tab, in the Export group, click More, and then click Merge it with Microsoft Office Word.

The Microsoft Word Mail Merge Wizard starts.

  1. If you have already created a document and you want to link your data to it, click Link your data to an existing Microsoft Word document. Otherwise, click Create a new document and then link the data to it.
  2. Click OK.

If you chose Link your data to an existing Microsoft Word document, the Select Microsoft Word Document dialog box opens. Select the document you want, and click Open. The document will be opened in Word.

If you chose to create a new document, Word opens with a blank document.

  1. In Word, follow the instructions in the Mail Merge pane. For more information about the Word Mail Merge Wizard, see Word Help.

 Note   You can use Word to print your addresses, regardless of where the addresses are stored. From Word, you can connect to address lists stored in a variety of programs, such as Excel, Outlook, and Access. Or, if you don't have an address list, you can create one in Word and then print labels. For more information, see Word Help.

Top of Page Top of Page

 
 
Applies to:
Access 2007