Concatenating

[Sammie]

2 questions:

1. Why does the following statement work on a report, and not work on
the form that us used to generate the data for a report?

=IIf([ShipSparesInTransit]=-1,"SHIP'S SPARES IN TRANSIT" & Chr(13) &
Chr(10) & [ShipCompany],[ShipCompany])

[ShipCompany] is missing in the first scenario on my form. It prints
correctly on my report.

2. Is there a way to concatenate lines in a report? I have already
concatenated all the fields I can, but the number of lines I have still
varies. I have the following address fields:
ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode (1 line, concatenated)
ShipCountry

The problem is that the ShipName is sometimes 2 lines, and the
ShipAddress is sometimes 2 lines. I could separate ShipAddress into
ShipAddress1 and ShipAddress2 if I could enable my address box to shrink
if there's nothing for the second line. Right now, I have
ShipAddress set to "can grow" but I still need to allocate the space on
the report. Any ideas?
Thanks.
Sammie

 

[Guest]

Sammie -

You need to use the IIF([Test Expression],[True Part],[False Part])
function, in all of your ship fields. Like this:

ShipName
=iif(isnull([ShipName2]),[ShipAddress],[ShipName2])
=iif(isnull([ShipName2]),[ShipCity ShipRegion ShipPostalCode],[ShipAddress])
=iif(isnull([ShipName2]),[ShipCountry],[ShipCity ShipRegion ShipPostalCode])
=iif(isnull([ShipName2]),"",[ShipCountry])

This assumes that you will always have a ShipName.

HTH - Kevin