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:
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.....
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.
100 Main Street
P.O. Box 123
Oklahoma City, OK 73106
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.
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
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: firstname.lastname@example.org