Inserting a Hard return in a expression on a report?

[Guest]

I have 10 fields that I am putting together in an expression and I want to
put a hard return between them, is this possible? The reason why I am doing
this is because if one of the fields is null then I want it to move up?

Expression: "FMV" & format([Fair Market Value Offer], "currency") & " date
paid " [FMV Offer Dt] etc.....

Example: FMV is $250,000.00 date paid 1/3/2005
RHP is $9,166.00 date paid 1/5/2005
RAP is #$1,025.00 date paid 1/7/05

For instances if RHP line is null then I want RAP to move under FMV. And I
can't use the IIF(Is Null.... because it leaves a gap so I thought of putting
it all together in a string. Is this possible???

Thanks for your help!!!

 

[Rick B]

Set the CAN SHRINK property.

Or, make each field zero-height and set the CAN GROW to true.

Rick B

 

[Rick B]

Note, you can use a return and keep it all in one statement, but you would
still end up with three lines. You would need to place the line feed in an
IF statement so it would only do it if needed. Personally, I'd just use the
can grow or can shrink and make them three separate fields.


= "This is line 1." & chr(13) & chr(10) & "This is line 2."













Rick B

 

[John Vinson]

I have 10 fields that I am putting together in an expression and I want to
put a hard return between them, is this possible? The reason why I am doing
this is because if one of the fields is null then I want it to move up?

Expression: "FMV" & format([Fair Market Value Offer], "currency") & " date
paid " [FMV Offer Dt] etc.....

Example: FMV is $250,000.00 date paid 1/3/2005
RHP is $9,166.00 date paid 1/5/2005
RAP is #$1,025.00 date paid 1/7/05

For instances if RHP line is null then I want RAP to move under FMV. And I
can't use the IIF(Is Null.... because it leaves a gap so I thought of putting
it all together in a string. Is this possible???

Thanks for your help!!!

There's a sneaky trick: the & operator treats a NULL field as a zero
length string, whereas the + operator concatenates strings but
"propagates Nulls" - if either argument to + is NULL the whole result
becomes NULL.

You can use a combination of & and + operators and parentheses to do
this:

Expression: ("FMV is " + Format([Fair Market Value Offer], "currency")
+ " date paid " + [FMV Offer Dt] + Chr(13) + Chr(10))
& ("RHP is " + Format([... <etc> )

That is, + operators inside each line so if either of the table fields
is NULL the entire line becomes NULL. The combination of Chr(13) and
Chr(10), in that order, is a new line command.

The alternative suggestion of separate textboxes with Can Grow/Can
Shrink can benefit from the same trick. Just use the parenthetical
expression as the control source of each textbox.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Thanks Rick! Ya'll are such great Help!!!

Note, you can use a return and keep it all in one statement, but you would
still end up with three lines. You would need to place the line feed in an
IF statement so it would only do it if needed. Personally, I'd just use the
can grow or can shrink and make them three separate fields.


= "This is line 1." & chr(13) & chr(10) & "This is line 2."













Rick B

I have 10 fields that I am putting together in an expression and I want to
put a hard return between them, is this possible? The reason why I am doing
this is because if one of the fields is null then I want it to move up?

Expression: "FMV" & format([Fair Market Value Offer], "currency") & " date
paid " [FMV Offer Dt] etc.....

Example: FMV is $250,000.00 date paid 1/3/2005
RHP is $9,166.00 date paid 1/5/2005
RAP is #$1,025.00 date paid 1/7/05

For instances if RHP line is null then I want RAP to move under FMV. And I
can't use the IIF(Is Null.... because it leaves a gap so I thought of putting
it all together in a string. Is this possible???

Thanks for your help!!!

 

[Guest]

Thanks John!!! I appreciate the additional "Sneaky Trick" way of doing it...
This helps me a ton....

I have 10 fields that I am putting together in an expression and I want to
put a hard return between them, is this possible? The reason why I am doing
this is because if one of the fields is null then I want it to move up?

Expression: "FMV" & format([Fair Market Value Offer], "currency") & " date
paid " [FMV Offer Dt] etc.....

Example: FMV is $250,000.00 date paid 1/3/2005
RHP is $9,166.00 date paid 1/5/2005
RAP is #$1,025.00 date paid 1/7/05

For instances if RHP line is null then I want RAP to move under FMV. And I
can't use the IIF(Is Null.... because it leaves a gap so I thought of putting
it all together in a string. Is this possible???

Thanks for your help!!!

There's a sneaky trick: the & operator treats a NULL field as a zero
length string, whereas the + operator concatenates strings but
"propagates Nulls" - if either argument to + is NULL the whole result
becomes NULL.

You can use a combination of & and + operators and parentheses to do
this:

Expression: ("FMV is " + Format([Fair Market Value Offer], "currency")
+ " date paid " + [FMV Offer Dt] + Chr(13) + Chr(10))
& ("RHP is " + Format([... <etc> )

That is, + operators inside each line so if either of the table fields
is NULL the entire line becomes NULL. The combination of Chr(13) and
Chr(10), in that order, is a new line command.

The alternative suggestion of separate textboxes with Can Grow/Can
Shrink can benefit from the same trick. Just use the parenthetical
expression as the control source of each textbox.

John W. Vinson[MVP]

John W. Vinson[MVP]