Address Block frustration

[gico1972]

I need some urgent help with a report. I have created a standard letter and the address field block is giving me some problems.

The letter is sent to companies and direct customers (No company name). In my database if we have a direct customer we populate the Company Name Field with the passengers name which is where the problem starts.

Therefore what i want to do is create an address block but remove the Company Name field if the underlying query the Direct Customer Field shows "Yes"

At the moment my address block looks like this if it is a direct Customer.

[Name] Mr A Smith
[Company] Mr A Smith
[Address] 100 Long Lane

Any help is very much appreciated.

 

[vbaInet]

Why not concatenate the Company and Address fields together? Then use an IIF() condition to check whether Company is zero-length string or Null.

 

[gico1972]

Thanks this has helped me better understand however i still getting a blank line where the company name should be using the following:-

My Database used a check box to determine if the record is a company or direct customer. if the box is checked then i want the address block to ignore the company Name field and remove the blank line.

=[Full Name] & Chr(13) & Chr(10) & IIf(([Private])="0",[Company Name]) & Chr(13) & Chr(10) & IIf(IsNull([Address 1]),"",[Address 1] & " " & Chr(13) & Chr(10)) & IIf(IsNull([Address 2]),"",[Address 2] & " " & Chr(13) & Chr(10)) & IIf(IsNull([Address 3]),"",[Address 3] & " " & Chr(13) & Chr(10)) & IIf(IsNull([Town / City]),"",[Town / City] & Chr(13) & Chr(10)) & IIf(IsNull([PC]),"",[PC] & Chr(13) & Chr(10))

 

[vbaInet]

Ok, you've added a new requirement - supressing blank address lines. So in light of this go back to your previous setup of having each field on seperate lines.

For the [Company] field, use this as the Control Source:

=IIF([Direct]=-1, Null, [Company])

Set the Can Grow and Can Shrink properties of the Company text box to Yes.

Set the Can Grow and Can Shrink properties of the Address fields too. Set the Can Shrink property of the Name textbox to No.

Let us know if that works for you.