Formatting Address Lines

[Stacey]

I have a report set up as a letter drawing fields from a table.
There are 4 address fields that should display as follows
Address
Address1
Address2
Address3
Then
State, City Zip

Some letters will use all 4 fields but some will only use one. My problem is how to format these fields so that there is not a big space between Address and State if Address1-Address3 are not used. Is there a way to do this?

Thanks
Stacey

 

[yippie_ky_yay]

Hi Stacey,

select all of your textboxes in the detail section and set their "CanGrow" and "CanShrink" properties to "Yes".

-Sean

 

[Stacey]

Thanks, that got my address problem fixed. However, I have the same problem in the body of the letter that it did not fix. The body of the letter has fields that are based on a value in a field from the table. The text should show up if the value is true, but be blank if the value is false. I tried setting the CanGrow/CanShrink on those as well, but I'm still getting a big blank spot in the middle of my letter if the value is false. (There are three fields like that on the letter). Any suggestions on how to fix that?

Thanks for all your help
Stacey

 

[smed]

use vba to hide/show the textbox

Create an "On Activate" event procedure in your report and specify a condition for displaying the text box.

eg.


If IsNull ([forms]![SomeForm]![SomeField]) THEN
Me.TextBox.Visible = False
Else
Me.TextBox.Visible = True
Endif

---------------------------------------------------------------

If ([forms]![SomeForm]![SomeField]) = False THEN
Me.HideableTextBox.Visible = False
Else
Me.HideableTextBox.Visible = True
Endif

---------------------------------------------------------------

If ([forms]![SomeForm]![SomeCheckBox]) = -1 THEN
Me.HideableTextBox.Visible = False
Else
Me.HideableTextBox.Visible = True
Endif



Hope this helps

Smed

 

[ddmcn]

You can also use the TRIM function to take out any trailing spaces. For instance:

=Trim([Address1])
=Trim([Address2])
=Trim([City])&", "&([State])&" "&([Zip])

In the body of your letter, just apply the same function to the text field. If it is null there will be no space there.

I think

Dennis

 

[Stacey]

This is what I have in the control source for the body of the letter:

=IIf([FIT]=True,"Federal Income Tax has been withheld in the amount of","") & " " & Format$([FIT Amount],"$#,##0.00\.")

How do I fit the Trim function in if the FIT and FIT Amount fields are null in the table?

I actually have three different fields one right after the other on the letter. One or all of them could be blank, and I need the embedded text to move up so there is not a big space. I've got it to move up if one field is blank, but if all three are, it leaves a space where the text field is on the report.

I tried the On Activate procedure on the report, but that didn't seem to do anything. I may have been doing it wrong, though.

 

[Rich]

=[Field1]&" "&IIf([FIT]=True,"Federal Income Tax has been withheld in the amount of","") & " " & Format$([FIT Amount],"$#,##0.00\.") &" "&[Field2]

 

[Stacey]

Got it, thank you all for your patience and help.

Stacey