INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MS Access Reports - Omitting Blank Lines

MS Access Reports - Omitting Blank Lines

(OP)
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)))))))

RE: MS Access Reports - Omitting Blank Lines

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.

RE: MS Access Reports - Omitting Blank Lines

(OP)
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

RE: MS Access Reports - Omitting Blank Lines

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

CODE

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 

CODE

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]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close