Formatting form address fields

[PolarBear]

I am trying to create address information of a form and I am using the data from the folowing fields:

Address_1
Address_2
City
State
Zip

If there are morre than 2 characters in Address_2 field then I want to carriage return and place this information on the second line of the address.

Of course if there is no data in field Address_2 I do not want to leave a blank line in the address information.

At present time I am using the following expression but I am not getting proper carriage returns. Any suggestions?


=[Address_1] & Chr(10) & Chr(13)
IIF (LEN([Address_2])>2,[Address_2]) & Chr(10) & Chr(13) & [City] & " " & [State] & " " & [ZIP]

 

[WayneRyan]

Polar,

If [Address_2] is 0 or 1 characters, I put it in parentheses.

=[Address_1] & Iif(Len([Address_2]) > 2, vbCrLf & [Address_2], "(" & [Address_2] & ")") & vbCrLf & [City] & ", " & [State] & "  " & [ZIP]

Wayne

 

[PolarBear]

Wayne,

Thanks for your reply. The code you provided generates an address however, the complete address appears on a single line. I am not getting any carriage returns.When I try to run the Address report based on the address query I am prompted to enter a paramater value for VbCrLf. In addition the text box that contains the addres information now has the VbCrLf code enclosed in Square brackets [VbCrLf]

Any addtional suggestions to get this working correctly? I am using Office XP

 

[WayneRyan]

Polar,

The vbCrLf is just a Visual Basic constant that stands for:

Chr(10) & Chr(13)

which is a carriage return and line feed combination.

I have no idea why it has no value on your system. Access
encloses it in square brackets when it asks for it is because it
thinks that it is of your creation.

You can put in the Chr values above to make it work, but it
would be "nice" to figure out why your system behaves this
way.

Wayne