Using <br> Tag in Rich Text Box

[Gina]

Hi,

I am creating a client letter in a report in Access 2010 and I want to take advantage of the rich text format. I created a query in the background of the report and have several concatenated fields:

Name1 = [FName] & " " & [LName]
Name2 = [FName1] & " " & [LName1]
Address = [City] & ", " & [State] & " " & [Zip]

In a text box formated with Rich Text I entered:

=[Name1] & '<br>' & [Name2] & '<br>' & [Address]

Which translates to:
Name1
Name2
Address

However, if there is no Name2, it shows:
Name1

Address

I have also tried =[Name1] & '<br>' + [Name2] & '<br>' & [Address] with the same results.

If I delete the <br> tag between [Name2] and [Address] it works, but then the Address is on the same line as [Name2]:

Name1 Name1


Address Name2 Address

How do I write the formula to skip the blank space? Any suggestions is greatly appreciated!

[ItsMe]

Edit

I did not read the post correctly. I would do this in VBA. Check for null and have two concatenated strings as options.

[Gina]

Hi ItsMe,

I tried vbcrlf previously and I receive an Enter Parameter Value (vbcrlf) error and when I go back into design view the code converts to below:
=[Name1] & [vbcrlf] & [Name2] & [vbcrlf] & [Address]

[ItsMe]

I edit my post. vbcrlf is a VBA constant. So it does not belong in a control source argument.

The only option I see (for your original question) is to do some validation in your VBA module. Once you determine whether or not there is a value for Address 2, you can assign the appropriate string to the control.

[Gina]

Could you be a bit more specific?

[ItsMe]

The VBA would look something like this. Tried to think of a way that would look intuitive so you could move it around and change it if needed.

Dim strAddress As String
strAddress = [FName] & " " & [LName] & vbCrLf
    If IsNull(FName1) = False And IsNull(LName1) = False Then
        strAddress = strAddress & [FName1] & " " & [LName1] & vbCrLf
    End If
strAddress = strAddress & [City] & ", " & [State] & " " & [Zip]
Me.txtControlName.Value = strAddress

The trick with reports is to determine where your controls reside within the report. You may have to do validation in one event handler (where say Fname is) and use another handler for another section to assign a variable to a control in a different section, like Page Header or Report Footer.

If all of the controls are in the same section all of the code in the example here will go in the same "Format" event handler.

You can create a handler by using the "Code Builder" or opening the VBA editor directly.

Highlight the section by clicking on it, the bar that runs horizontaly across the report's design surface. Go to the property sheet and click the Event tab. There will be a Format event. Click the ellipses (...) next to Format and select code builder.

[amrut]

Here is one more alternative -
Add a text box (Plain text -high enough to accommodate all the fields) to your report and set its control source to -
=[Name1] & IIF(Len([Name2]>0,Chr(13) & Chr(10) & [Name2],"") & Chr(13) & Chr(10) & [Address]
Hide your bound fields Name1,Name2 and Address

[ItsMe]

There you go. I don't know why I decided to not consider instant if as an option. I guess I am too much of a creature of habit.

[Gina]

Thank you for your responses.

I originally had a Plain formated text box using chr(13) and chr(10) for my carriage returns and it worked. I then decided I wanted my entire letter in the unbound text box for formating purposes. The document needs bolding and other formating. The one nice thing about now having 2010 version is I do not need to have so many text boxes in my report as they never lined up as nicely as if you just typed it into word.

I tried concatenating the fields in the background query using chr(13) and chr(10), but when I entered it into my Rich Text box and forced everything onto 1 line.

[Gina]

I figured out what I was doing wrong. I was trying to format the null value in the report instead of doing it in the query. I changed the query to:

BothNames:[FName] & " " & [LName] +[FName1] & " " [LName1]

Then in the Text box, in my report, my code is:

[BothNames] & '<br>' & [Street] & '<br>' [Address]

Adding the (+) sign before FName1 in the query forced the concatenation to ignore null values.

I managed to type an entire letter with bolded fields etc in one text box.

Thank you for all suggestions.