How to concatenate a row in a Name/Address text box

[austinmoran]

I have created a fairly simple report in Access 2007 in which I include FirstName, MiddleName, LastName, OfficeAddress1, OfficeAddress2, OfficeCity, OfficeState, OfficeZip. It works fine if I create a separate text box for each line, with one exception. If there is no data, for example, in the OFFICEADDRESS2 field, then it obviously leaves a blank line and looks pretty cheesy. I have attempted to create a single text box and add the following code to the Control Source but I get an error. I had hoped that it would remove the blank line when a field is empty:

=IIf(IsNull([OFFICEADDRESS1]),"",[OFFICEADDRESS1] & Chr(13) & Chr(10)) & _
      IIf(IsNull([OFFICEADDRESS2]),"",[OFFICEADDRESS2] & Chr(13) & Chr(10)) & _
      IIf(IsNull([OFFICECITY]),"",[OFFICECITY] & ", ") & _
      IIf(IsNull([OFFICESTATE]) ,"",[OFFICESTATE] & " ") & _
      IIf(IsNull([OFFICEZIP]),"",[OFFICEZIP])

The error that comes up in the DESIGN view when I attempt to exit the text box is: The Expression you entered contains invalid syntax...etc, etc, etc

What am I doing wrong?

Thanks in advance for any/all assistance you might be able to offer.

-Austin

 

[GingerR]

not sure what all the &_ are for if this is just a text box on a report?

So you always want Address1, right? It's always filled in.
Address2 may or may not be filled in, right? If it is, show it. If it's not, don't show it and don't have a blank line. City, State and Zip are always filled in. For all of them except for Address2 you say:

"If it's blank, show blank, otherwise show whatever it is" which is essentially saying "show whatever it is, whether it's blank or not" so you don't need all of the "Iif(isnull)" stuff for all of the other fields except for Address2.



=[OFFICEADDRESS1] & Chr(13) & Chr(10) & IIf(Not (IsNull([OFFICEADDRESS2])),[OFFICEADDRESS2] & Chr(13) & Chr(10)) & [OFFICECITY] & ", " & [OFFICESTATE] & " " & [OFFICEZIP]

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[austinmoran]

Thanks for your helpfulness. It worked perfectly.

- Austin

 

[misscrf]

You can also make that section Can grow can shrink to yes, as well as the fields. Then use the =iif(isnull([OFFICEADDRESS2]),"",[OFFICEADDRESS2]) .

I Do that with my labels. I make them all textboxes, and then put that in there as the control source:

=iif(isnull([OFFICEADDRESS2]),"","Address 2")
Makes it so you don't have a label when there is no value.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

To hide a label, you can also use:
="Address 2 "+[OFFICEADDRESS2]
If the field is Null, the entire expression will be null.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[austinmoran]

Thank you all for your helpfulness! You made short work out of what seemed daunting!

Austin

 

[misscrf]

That's a good idea. I usually have my labels above, but this might work better.

Now, I'm liking this thread!

Thanks,

misscrf

It is never too late to become what you could have been ~ George Eliot