Results 1 to 5 of 5

Thread: Label printing

  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Label printing

    I'm trying to print labels in Microsoft Access 2003. First of all, my method of retrieving my data is I'm importing an ascii file into my database. The ascii file is generated from our mainframe. In the ascii file, I have the following fields:

    P&A Code
    Dealer Name
    Address
    City
    State
    Zip

    In the address field on certain records, I have only a street address. On other records, I have a street address plus a post office box. The street address and the post office box are separated by a semicolon (. What I want to do is on my label report on the records that have a street address and post office box, I want to have a carriage return after the street address without having to add another field. Here's an example.....

    06895
    John Doe
    100 Main Street; P.O. Box 123
    Oklahoma City, OK 73106

    I would like for it to look like this without having to add another field.

    06895
    John Doe
    100 Main Street
    P.O. Box 123
    Oklahoma City, OK 73106

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    You can add two additional fields in a query and manipulate the data by having the first field show the data left of the semicolon and the 2nd after the semicolon, using the Instr() and the Left$() and Right$() functions. Empty right fields will have a null value and won't print as labels eliminate empty fields.

    Having said that, USPS and Canada Post automated address processing have no problem with the street address and the PO Box being on the same line. For this, search the address field and replace the semicolon with a space. You just have to make sure that the length of the field does not exceed the label width. (Take it from someone who manages 100,000's of address data on a daily basis).

    If you need help in how to do it with a Query, upload a sample database with a few records and I will return it to you with the sample code within a short time.

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    Attached is a sample database with a few records. You can see in the field DLRSTREE that some addresses have a street address only and some have both a street address and a post office box. As I stated in my previous post, I would like to have the street address and the post office boxes on separate lines for the records where this is applicable. I have a rough layout in my reports section of the labels. Any help is greatly appreciated. Thank you very much for offering your assistance
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    I corrected the fields as requested, created a table of the Query as well and created a test module where I constructed the IIF statements extracting the values. I used the Module for syntax check. I hope this works for you, but frankly, I don't care for the bracketed zip codes in the address fields.
    You can remove them successively, after testing another query and then a new table (3 . . . .and on ). I should have mentioned that I make my living formatting and processing 100,000's of US and Canadian addresses on a daily basis.

    You will also have to recreate the labels as well since you forgot to specify to close up the empty address lines. If you need additional help you can email me directly: ineuw@aei.ca
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2004
    Posts
    9
    Thank you so much. Everything works great now. I appreciate your help greatly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •