Remove Null fields in a report in address

[richard]

Hi

I have a report which has clients name and address on it.
The problem is that I have the following fields

Clientname
address1
address2
address3
town
city
county
postcode
Not all clients have values in all fields. Therefore I am left with blank
spaces on the report, how can I remove these blank spaces. An example would
be as follows

clientname
address1
town
county
postcode

Any help greatly appreciated

Richard

 

[Ken Snell \(MVP\)]

In the query that provides the data for the report's RecordSource, create a
calculated field to give you the client with total address:

ClientTotalAddress: [Clientname] & (Chr(13) + Chr(10) + [address1]) &
(Chr(13) + Chr(10) + [address2]) & (Chr(13) + Chr(10) + [address3]) &
(Chr(13) + Chr(10) + [town]) & (Chr(13) + Chr(10) + [city]) & (Chr(13) +
Chr(10) + [postcode])

Then bind a single textbox in your report to this field. Make sure that the
textbox's CanGrow and CanShrink properties are set to Yes, and that the
CanGrow and CanShrink properties for the section containing that textbox
also are set to Yes.


The calculated field takes advantage of the fact that the + operator
propogates a Null value while the & operator does not. Therefore, for the
second through last fields in the expression, only those with values will
cause a new line to appear in the textbox's display.

 

[BruceM]

One possibility is to set the Can Shrink property for the text boxes to Yes.
Also, set the Can Shrink property of the Detail section (if that's where the
text boxes are) to Yes. You may also want to set the Can Grow property,
depending on your needs.
This may not work as you would like if there are other text boxes to the
right or left of the name and address text boxes. In that case (or in any
case) you can concatenate the information. One way is to use something like
this in the report's Print event (txtFullAddress is an unbound text box on
the report):

Me.txtFullAddress = Me.ClientName & _
vbCrLf & Me.Address1 & _
(vbCrLf + Me.Address2) & _
(vbCrLf + Me.Address3) & _
(vbCrLf + Me.Town) & _
(vbCrLf + Me.City) & _
(vbCrLf + Me.County) & _
(vbCrLf + Me.PostCode)

This assumes there is always an Address1, and that each field is on its own
line as you have demonstrated. When the + sign is used as the concatenation
operator, if any part of the expression is null the entire expression
evaluates to null. For instance, if there is no Address2 then (vbCrLf +
Me.Address2) evaluates to null.

You can use similar logic in a blank column in query design view, except
that you need to use:
Chr(13) & Chr(10)
instead of vbCrLf

FullAddress: Me.ClientName & _
Chr(13) & Chr(10) & Me.Address1 & _
(Chr(13) + Chr(10) + Me.Address2)
etc.

Note that you would not use the underscore characters in this case. They
are here for readibility only.

It's the same idea in the Control Source of an unbound text box, except it
starts with an = sign:
= Me.ClientName & _
Chr(13) & Chr(10) & Me.Address1 & _
(Chr(13) + Chr(10) + Me.Address2)
etc.