Optional Field

[danny123]

Hi there,

In one of my reports, i have a field naming "Company Name".
Now i want that this field should be visible only when there is value for this (Means its not null).
And in case the value is null then space for the field is also not used.
For example:

If Company Name if not Null:

Mr. Smith Lehman
ABC Corp.
1222 Tree View Road
NJ,12345.

If Company Name is Null then it should look like this:

Mr. Smith Lehman
1222 Tree View Road
NJ,12345.

and not like this:


Mr. Smith Lehman

1222 Tree View Road
NJ,12345.


Thanks in advance !

 

[Rich]

Something like
=[FirstName] & " " & [LastName] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & IIf(IsNull([City]),"",[City] & Chr(13) & Chr(10)) & [PostalCode]

as the control source for an unbound textbox

 

[LPurvis]

There are a few little ways of achieving your desired result.
You can put in a explicit check as Rich has shown (you'd perhaps need to do this for each field though - unless you can guarantee the others can't be Null).
You can accomplish much the same in code - assigning the value to an unbound textbox having performed the checking and concatenation in code (less "flat" logic but same idea).

You can also allow propagation of Null to handle the expression formatting for you
e.g.
=([NameField] + Chr(13) & Chr(10)) & ([Company Name] + Chr(13) & Chr(10)) & ([AddressField] + Chr(13) & Chr(10)) & [ZipCode]

Or you can maintain distinct controls for each field - drop their height to zero, place them immediately beneath each other and set their CanGrow property to true.