Mailing Label

Nadalia

Registered User.
Local time
Today, 17:41
Joined
Mar 23, 2004
Messages
25
help on mailing label, pls!

My mailing label layout looks like this:

MLabel
JobTitle
Company/Department
Address1
Address2
Address3
=Trim([City] & " " & [State] & " " & [ZipCode] & " " & [Country])


There are several fields that could be blank. They are JobTitle, Company/Department, Address1, Address2, Address3. I want to remove all blank lines from the label so they won’t look funny. How can I shrink the mailing label vertically? Any codes behind?

I'd really appreciate!

Nadalia
 
=Trim([JobTitle])
=Trim([Company/Department])
=Trim([Address1])
=Trim([Address2])
=Trim([Address3])
=Trim([City] & " " & [State] & " " & [ZipCode] & " " & [Country])

should work in the Detail section, but I don't think it works in the Header or Footer.

HTH
 
it did not work.

I tried, and it did work the way I want. For example, some of records do not have address2, and address3. They just have address1 and zip code, so my label likes this:

Fred Health
Vice Provost
General Libraries


Campus G9300


Look, there are two lines blank between Department and Address1 which is campus.

I used trim function ,but it did not work.

Any ideas?

Thanks in advance!

Nadalia
 
Do you have these controls in the Detail section of the report?
 
You should have 6 TextBoxes in the Detail Section, like...

TextBox1 Control Source...........=Trim([JobTitle])
TextBox2 Control Source...........=Trim([Company/Department])
TextBox3 Control Source...........=Trim([Address1])
TextBox4 Control Source...........=Trim([Address2])
TextBox5 Control Source...........=Trim([Address3])
etc.

HTH
 
___ said:
You should have 6 TextBoxes in the Detail Section, like...

TextBox1 Control Source...........=Trim([JobTitle])
TextBox2 Control Source...........=Trim([Company/Department])
TextBox3 Control Source...........=Trim([Address1])
TextBox4 Control Source...........=Trim([Address2])
TextBox5 Control Source...........=Trim([Address3])
etc.

HTH

I, actually, have 7 TextBoxes there now:

TextBox1 Control Source...........=Trim([MLabel])
TextBox2 Control Source...........=Trim([JobTitle])
TextBox3 Control Source...........=Trim([Company/Department])
TextBox4 Control Source...........=Trim([Address1])
TextBox5 Control Source...........=Trim([Address2])
TextBox6 Control Source...........=Trim([Address3])
TextBox7 Control Source...........=Trim([City] & " " & [State] & " " & [ZipCode] & " " & [Country])

Is anything to do with those textbox?

Nadalia
 
BTW,

I used label wizard in report object to create the mailing labels, so all the textboxes were automatically added.
 
Anybody can help? Please?

Thanks in advance!

Nadalia
 
Hm, not exactly sure the code behind it but you'll probably need a check to see if a field is NULL and if it is.... *ponders*...

If this was me, I'd either try one large expression with a carriage return after each field that wasn't null or I'd use VBA to gather the data but probably do the same type of operations as the expression would do. I'd expect there would be an easier way though.
 
Hi, It's you!Thanks for your reply.

I put the following in the control source for each address--

=iif ((isnull[Address1]), "", Trim([Address1]))
=iif ((isnull[Address2]), "", Trim([Address2]))
=iif ((isnull[Address3]), "", Trim([Address3]))

But after I click ok, and preview the label, the code I just put disappeared. The control source wend back to the default setting.

It seemed not working. Any ideas?

Thanx!

Nadalia
 
Delete the Textboxes the wizard created from the report and create your own from the ToolBox, then use the trim function I suggested in the detail section.
See Attached.

HTH
 

Attachments

Hi HTH,
I really appreciate your help. Your method worked beautifully to your attached db, but not to mine. I followed what you suggested: deleted all the textboxes that wizard created, and added unbounded textbox from toolbox, and type all the trim in there, I still got those annoying blank lines! I played with the page Setup, but my label still looked funny.

I attached my database, please take a look at your convenience and see if you get any idea!

You gonna save my life!

Nadalia
 

Attachments

Last edited:
I had a look at the sample and there seems to be something in your blank fields that's stopping the field from shrinking. I don't know if this has anything to do with it but in the table your Allow Zero Length property was set to Yes. I changed that to No and highlighted the "empty" fields in the table and hit delete - did this for the first few records and when I looked at the report again the fields were trimming down. I also use one large field that holds all the address information:

Client: IIf(IsNull([mlabel]),"",[mlabel] & Chr(13) & Chr(10)) & IIf(IsNull([jobtitle]),"",[jobtitle] & Chr(13) & Chr(10)) & IIf(IsNull([company]),"",[company] & Chr(13) & Chr(10)) & IIf(IsNull([address1]),"",[address1] & Chr(13) & Chr(10)) & IIf(IsNull([address2]),"",[address2] & Chr(13) & Chr(10)) & IIf(IsNull([address3]),"",[address3] & Chr(13) & Chr(10)) & IIf(IsNull([city]),"",[city] & ", ") & IIf(IsNull([state]),"",[state] & Chr(13) & Chr(10)) & IIf(IsNull(
Code:
),"",[code] & Chr(13) & Chr(10)) & IIf(IsNull([country]),"",[country])

I create a query for the report and then create a column with the above information and select that field for the report. 

Hope that helps

DBL
 
Tooooo weird!!!!!

Hi DBL and Others,

Thank you all for kindly help on this thing. I am still stuck in formatting mailing label layout.

These days, I've been playing with Trim function, IIF is null, and some other possible ways, but none of them worked. My mailing labels still looked funny with these annoying blank lines. I thought there might be something weird in my table, so I create new tables and set allow zero length to No, and import some records into this new table, and I created new mailing label based on this table. I tried three methods: Trim, FixLine function which is suggested by Access 2000 Developer's Handbook, and the method DBL provided, to create three mailing label report files, they all looked messy. Here is the link to my db file:

http://galsworthy.hrc.utexas.edu/cfmailing/test.mdb

Please someone take a look at it, I am so gonna die on this!

Thanks!

Nadalia
 
For some reason the fields which look blank in the table have a space, or something, in them. I went through the blank fields individually and hit delete and then ran the report and the fields are trimming as you would expect. Don't know if there is any easy way to take out whatever is lurking in the background. I tried running an update query for the IsNull fields with a Null update but that didn't work either, just the long way round of deleting the content of the fields in the table.
 
You're right. I did same thing, and it was trimming down. It is the weirest thing ever in my life. But I have over 4000 records in my database, am I gonna manually do this for all these records?! God, kill me!

I appreciate your time, DBL. Have a great rest of the day!

Nadalia
 
My day's over, I'm off to bed! Hope someone else comes up with a quick solution for you.

DBL
 

Users who are viewing this thread

Back
Top Bottom