Not to print blank fields

[Agent Ting]

Hi all,

I have in my designed report 5 fields, say Note1, Note2 and so on.
Sometimes not all fields are filled in, hence I have a problem trying to NOT
print the fields that are empty, as it always crosses another page showing
nothing.

Does anyone how a code could help to do this?

Many thanks.

 

[Rick B]

Well, typically if you have up to five entries for an item, and each entry
is interchangeable, you would not create five fields for it. This is called
a one-to-many relationship. Instead, you create a separate related table
where the user can add one or more records. For example, lets say that you
can enter one or more notes per "customer". You'd have a customer table,
and a notes table. You'd have one record per customer in the customer
table, and one or more records per customer in the NOTES table. The two
tables would be related through the use of the customer number field...

TblCustomers
CustomerNumber
CustFirstName
CustLastName
CustAddress1
CustAddress2
CustCity
CustState
CustZIP
etc.


TblNotes
CustNumber
NoteDate
NoteText


Once you "normalize" your database, you will find it much easier to produce
queries, forms, and reports without having to jump through hoops to get it
to format the way you want.

Hope that helps.

 

[BruceM]

Try setting the Can Grow and Can Shrink property of the text boxes and of
the Detail section (if that is where the text boxes are located) to Yes, and
make the text boxes tall enough for one row of text. If the text boxes are
side by side, or if there are other text boxes to the left or right, this
may not work as expected, as the text boxes to either side will grow whether
you mean them to or not.
An option if the above does not work is to concatenate the text. In the
Detail section you could have a text box named txtNotes; in the Detail
section's Print event, you could then have something like:
Me.txtNotes = [Note1] & (vbCrLf + [Note2]) & (vbCrLf + [Note3])
vbCrLf takes you to the next line (carriage return and line feed). By using
the plus sign, if any part of the expression within parentheses is null the
entire expression within the parentheses evaluates to null. For instance,
if Note2 is null, the code skips to Note3 without leaving a blank line.