Concatenation and IIF statements in report

[bjsbrown]

Have searched and spent many hours but can't fix - any help appreciated. Use 2000 and vista. Have an address1 field, address2 field, city field, state field, and zipcode field. I can concatenate the city, state and zip field like it needs to be in the report but the problem is most of the records have nothing in the address2 field so i need to eliminate showing a blank line while still concatenating the remainder. If the IIF statement is correct to use, how to write it in relation to the remainder of the address being concatenated.

[RuralGuy]

Post the concantenation line you are currently using, please.

[CGM3]

Off the top of my head:

IIf(IsNull([address2]),"",Trim([address2]))

If you're using commas or line feeds as delimiters, check to see if Trim([address2]) is more than "" before adding.

IIf(IsNull([address2]),"",IIf(Trim([address2])="","",", " & Trim([address2])))

[bjsbrown]

Here you go, Rural Guy
=Trim([MemCity] & " " & [MemSt] & " " & [MemZip])
Gives result of Evans GA 30809

[RuralGuy]

I was looking for how you were contantenating the address1 and address2 fields. Are you adding in CarriageReturns and LineFeeds?

[bjsbrown]

I'm not; that's the problem, I guess. I don't know how to write whatever is needed to get rid of the extra line if there is no value on address2. It's been a long time since I tried to do this so am a little rusty. I will say no to carriage returns and line feeds. I also tried what CMG3 suggested but didn't work.

[RuralGuy]

Is this in a report? Are you trying to get the address on either three or four lines? Is there anything else on the line with address2?

[bjsbrown]

Yes a report to come out as follows:

John H Smith (Name of Client field)
2546 Andrews Drive (Address1)
Suite 450B (Address2)
Atlanta (City Field) GA (State Field) 30434 (Zip Field)

If there is no address2 data, go to City, State, Zip rather than leaving an empty line.

[RuralGuy]

If there is nothing else on the line horizontally and the Detail Section is set to CanShrink then you can manipulate the .Visible property of the control in the Print Event of the Detail Section.

If Len(Me.Address2 & "") = 0 Then
   Me.Address2.Visible = False
Else
   Me.Address2.Visible = True
End If

[bjsbrown]

Haven't been able to get to work yet. Does it go in the query or report?

[RuralGuy]

It goes in the Print Event of the Detail Section of the Report.

[bjsbrown]

Received the following: The expression on print you entered as the event property setting produced the following characher: Invalid character.

I've tried playing around with it but can't get to work.

[RuralGuy]

Did you press the "..." button and put in the code? Post what you have here so we can look at it.

[bjsbrown]

Yes to question- I just copied and pasted

If Len(Me.Address2 & "") = 0 Then
Me.Address2.Visible = False
Else
Me.Address2.Visible = True
End If
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
End Sub

[RuralGuy]

The code needs to be in the Section where the control is located. Is your control in the GroupHeader1? It *must* also be AFTER Private and BEFORE End Sub. You put everything before Private.