Create mailing labels in Access

If you want to print addresses that are stored in an Access database, or you want to store and then format and print them using Access, continue to the next section, Creating and printing labels using Access.

If not, continue here:

ShowPrint Access data from Microsoft Office 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, see the following:

ShowPrint using a program other than Access

If your addresses are stored in Microsoft Office Excel or Microsoft Office Outlook, you can use the Microsoft Office Word Mail Merge Wizard to print them. Or, you can connect to data in other programs directly from Word.

Use the Word Mail Merge Wizard to print addresses stored in Excel    

If the addresses you want to print are stored in Excel, you can use the Word Mail Merge Wizard to print them. For step-by-step information on how to do this, see Print labels by using Excel data in a Word mail merge.

Use the Word Mail Merge Wizard to print addresses stored in Outlook    

If the addresses are stored in the Contacts folder in Outlook, you can use the Word Mail Merge Wizard to print them. For step-by-step information on how to do this, see the following Help topics:

Use Word to print addresses (without using the Mail Merge Wizard)    

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 Outlook, Access, and so on. Or, if you don't have an address list, you can create one in Word and then print labels. For more information, see the following Word Help topics:

Creating and printing labels using Access

Start by creating a simple report that is formatted to look like a mailing label. The report gets the address data from the table or query that stores the addresses. Printing the report will give you a single label for each address in the underlying record source.

Use the Label Wizard to create the report. You can then customize the layout of the labels, or write Visual Basic® for Applications (VBA) code to extend the report to meet your needs. The last step would be to preview the labels.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Click the table or query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) that contains the data for the labels, and then click OK.
  2. Follow the instructions in the wizard.

ShowChoose a label size in the Label Wizard

On the first page of the Label Wizard, you can choose among many predefined label sizes.

  1. In the Label Wizard, make the Filter by manufacturer box appear by clearing the Show custom label sizes box.
  2. Choose the manufacturer in the Filter by manufacturer box, and select your label in the What label size would you like? box.
  3. Select Sheet feed or Continuous under Label Type, and click Next.
  4. On the What would you like on your mailing label page of the wizard, choose the fields you want in your report from the Available fields, and double-click them to add to the Prototype label box.
    • 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.
  5. Click Finish.

 Note   Often, you might want to print the addresses directly on the envelopes instead of printing them on 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 create a custom label. Also remember to set Label Type to Sheet Feed. An easier way of printing on envelopes is to export the addresses to Word and use the mail merge functionality of Word. For more information, see Create and print envelopes for a mass mailing, in Word Help.

How to skip used mailing labels and print duplicates. This article also includes information about how to print on a partially used page of labels where only some of the labels are available.

  • Avoid duplicate labels     If your list contains multiple people with the same address, you might not want to print separate labels for each of them. Instead, you might want to merge the records, by printing the different names on the same label, like this:
    Name 1
    Name 2
    Street address
    City
    State
    Zip code

For step-by-step information on how to merge records, see the Knowledge Base article on the Microsoft Product Support site:

Sample query to print one label for two people at the same address.

  • Skip used labels on a partially-used page    If you want Access to skip some label spaces and print labels on the unused portion of a page, see the Knowledge Base article on the Microsoft Product Support site:

How to skip used mailing labels and print duplicates.

  • 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.

For step-by-step information about creating a parameter query, see Using parameters with queries and reports. For information on changing a report's record source, see Change the record source or connection information.

If you are using Word to create and print the addresses, specify the parameter query as the data source during the mail merge operation. For more information about how to do this, see the section "How to Perform a Mail Merge Operation by Using an Access Parameter Query" in the Knowledge Base article on the Microsoft Product Support site:

Use Mail Merge with an Access parameter query in Word 2000.

  • 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.
  • Preview the labels Open the label report and preview it the same way you would preview any other report. For more information, see Preview a report.

 Note   If one or more controls on the report display "#Error" instead of the address data, see the Knowledge Base article on the Microsoft Product Support site:

Troubleshoot labels created using the Label Wizard.

  • Print the labels     To print the labels, open the label report and on the File menu, click Print.

For more information, see Print a report.

 
 
Applies to:
Access 2003