MS Access Reports - Omitting Blank Lines

[littlehoa]

HELP!!! This should be easy, but doesn't seem to be for me.
I need to create a report containing directory information - names, addresses, phone numbers, cell numbers, email addresses, etc.
Not all records have values in every field.
How can I eliminate the white space left when there is an empty field?
I currently have several fields referenced in one text box - see below.
What I'm getting is no data at all once a null field is encountered. For example:
When the cell2 field is null then nothing else prints after the FirstName2 field.
Sorry for my rustiness with Access report - Thanks in advance!

Current text box syntax:

=IIf(IsNull([FirstName2]),"",RTrim([FirstName2] & Chr(13) & Chr(10)) & IIf(IsNull([Cell2]),"",RTrim([Cell2]) & IIf(IsNull([work2]),""," " & [work2] & Chr(13) & Chr(10)) & IIf(IsNull([Email2]),"",RTrim([Email2] & Chr(13) & Chr(10)) & IIf(IsNull([Child1]),"",[Child1] & IIf(IsNull([Child2]),""," " & [Child2] & Chr(13) & Chr(10)) & IIf(IsNull([Child3]),"",[Child3] & IIf(IsNull([Child4]),""," " & [Child4] & Chr(13) & Chr(10)))))))

 

[MajP]

Although I am not sure of your problem, can you explain how this is supposed to look with missing values? I doubt you will get what you expect.
This is how it would look with values for all fields I believe.

FirstName
Cell# Work#
Email
Child1 Child2
child3 Child4

Now (to include empty lines and empty spaces)
1) Show what you expect if the work # is missing
2) Show what you expect if email is missing
3) show what you expect if work and cell is missing

Using a user defined function you can probably account for these, but need to know the intent of sliding things left and or moving to previous line.

 

[littlehoa]

All good questions! Thanks.
1) Missing work#
FirstName
Cell#
Email
Child1 Child2
child3 Child4
2) missing email
FirstName
Cell# Work#
Child1 Child2
child3 Child4
3)missing work and cell
FirstName
Email
Child1 Child2
child3 Child4

 

[MajP]

Your code should work, I am not seeing what is wrong. However the logic would give problems for a couple of cases.

1) Missing work# (There is no CRLF so email moves up. May or not matter)

FirstName
Cell# Email
Child1 Child2
child3 Child4

2) Missing Cell (unwanted space before work)
FirstName
Work#
Email
Child1 Child2
child3 Child4

Now to account for these minor problems seems to take a lot of extra. This may be overkill but here is a UDF. You can use this by passing in fields or control names. I used literals to test and the formatting all looks good

Public Function ConcatInfo(Fname As Variant, work As Variant, cell As Variant, email As Variant, child1 As Variant, child2 As Variant, child3 As Variant, child4 As Variant) As String
  Dim strLine As String
  ConcatInfo = Trim(Fname & " ") & vbCrLf
  strLine = Trim(Trim(cell & " ") & " " & Trim(work & " "))
  If strLine <> "" Then ConcatInfo = ConcatInfo & strLine & vbCrLf
  If Trim(email & " ") <> "" Then ConcatInfo = ConcatInfo & Trim(email) & vbCrLf
  strLine = Trim(Trim(child1 & " ") & " " & Trim(child2 & " "))
  If strLine <> "" Then ConcatInfo = ConcatInfo & strLine & vbCrLf
  strLine = Trim(Trim(child3 & " ") & " " & Trim(child4 & " "))
  If strLine <> "" Then ConcatInfo = ConcatInfo & strLine & vbCrLf
End Function

Public Sub testconcat()
 Debug.Print ConcatInfo("Smith", "123-456-7891", "987-654-3210", "john.smith@access.com", "Timmy", "Tommy", "Susie", "Genny")
 Debug.Print ConcatInfo("Smith", Null, "987-654-3210", "john.smith@access.com", "Timmy", "Tommy", "Susie", "Genny")
 Debug.Print ConcatInfo("Smith", "123-456-7891", Null, "john.smith@access.com", "Timmy", "Tommy", "Susie", "Genny")
 Debug.Print ConcatInfo("Smith", Null, Null, "john.smith@access.com", "Timmy", "Tommy", "Susie", "Genny")
 Debug.Print ConcatInfo("Smith", "123-456-7891", "987-654-3210", Null, "Timmy", "Tommy", "Susie", "Genny")
End Sub

Smith
987-654-3210
john.smith@access.com
Timmy Tommy
Susie Genny

Smith
123-456-7891
john.smith@access.com
Timmy Tommy
Susie Genny

Smith
john.smith@access.com
Timmy Tommy
Susie Genny

Smith
987-654-3210 123-456-7891
Timmy Tommy
Susie Genny

to use in a query or calculated control

concatinfo([firsname2],[cell2],[work2],...[child4]