Mailing Labels IIf and blank lines (1 Viewer)

[StephaniaFregosi]

Label Problem

=Trim([FirstName]) & “ “ & [LastName] & Iif(IsNull([OrganizationName]), “ and “ ([OrganizationName)]
=Trim(IIf(IsNull([OrganizationName]),[Address1],[OrganizationName]))
=Trim(IIf(IsNull([OrganizationName]),[Address2],[Address1])) Or IIf(IsNull([Address2]),[City] & " " & [StateAbbreviation] & " " & [Zip])
=Trim(Iif(IsNull(OrganizationName]),

I have two fields that could be blank in my address table. One is Organization Name and one is Address2. I want to remove all blank lines from the label so they won’t look funny. I really would like to avoid 1) running two queries or 2) mail merging this thing into word (where I have no idea how to do line1)

I was doing fine with lines 1 and 2. Line 3 should be something like well if organization name is blank give me address 2, if not give me address 1. But what do I do if address 2 is blank!

Thanks,

Stephania

 

[KevinM]

Remove all your fields from the report and insert an unbound text box. Resize this text box to just smaller than the whole detail.

Create a new module and paste this in...

Function AddressBlock$(Addr1, Addr2, Addr3, Addr4, Addr5, Addr6, Addr7)

Dim A1$, A2$, A3$, A4$, A5$, A6$, A7$, CR$
CR$ = Chr(13) & Chr(10
A1$ = IIf(ISB(Addr1), "", Addr1 & CR$)
A2$ = IIf(ISB(Addr2), "", Addr2 & CR$)
A3$ = IIf(ISB(Addr3), "", Addr3 & CR$)
A4$ = IIf(ISB(Addr4), "", Addr4 & CR$)
A5$ = IIf(ISB(Addr5), "", Addr5 & CR$)
A6$ = IIf(ISB(Addr6), "", Addr6 & CR$)
A7$ = IIf(ISB(Addr7), "", Addr7 & CR$)
AddressBlock = A1$ & A2$ & A3$ & A4$ & A5$ & A6$ & A7$ 'Concatenate the strings.
End Function

Function ISB(V) As Integer
If IsNull(V) Or V = "" Then ISB = True Else ISB = False
End Function

NOTE: This is for SEVEN Address fields so you will need to adjust it to the number of fields you require in the text box.

In the unbound text box I mentioned in your report put this in the conttolsource....

=AddressBlock ([FirstName]&[LastName],[OrganizationName],[Address1],[Address2],[City],[StateAbbreviation],[Zip])

The number of fields MUST match the number of fields in the above function, seven in this example.

No matter which fields are blank, the address will always look correctly proportioned.

HTH

Kevin M

 

[Chris RR]

Actually, what you need to do is relax. Then, take a look at the "can shrink" property in Help. What it does is to make the line shrink "vertically so that the data it contains can be printed or previewed without leaving blank lines." Put OrganizationName on its own line, set "can shrink" to yes, and you should be set.

 

[StephaniaFregosi]

Can shrink is great, but I do have some stuff to the right of one of my reports.

Address info Telephone Info

so it won't shrink if there's stuff on the right.

If you know of away around that, I would prefer your method to building a module.

Thanks,

Stephania

 

[KevinM]

Stephanie

The CanShrink property is USELESS when it comes to multiple address fields.

Trust me....use the module, you won't regret it (just copy and paste it in)

I bet you will you use it time and time again once you have it in your db.

 

[StephaniaFregosi]

Works like a charm. Thanks very much Kevin.

And, for the rest of the world, I used Kevin's exact module with the following string.

7 turned out to be the # of blocks of items.

=AddressBlock([FirstName] & " " & [LastName],[OtherName],[OrganizationName],[Address1],[Address2],[City] & ", " & [StateAbbreviation] & " " & [Zip],[Country])

 

[KevinM]

Thanks Stephania

I got the module from the A97kb so I will take only part of the credit!

Now that you have this module you will probably find you will use time and time again in several dbs. It is second to none when it comes to printing labels or letter headers.

The only thing to remember is that if you use it in other dbs make sure you add or remove Address lines in the code to match the number in your table/query.

PS
If you ever come across Pat Hartman on this forum please let them know about this module !