Eliminate blank lines / Do not display text if matching criteria

[Lucretia]

Hello,

I'm very new to Access - - I don't know much about it except I've been asked to create a report from an already existing DB.

In my report, there are 4 fields (location, call number, online availability, subject) that can sometimes be empty. They appear one above the other on the right side of my report. Now, when there is no value for one field or the other, the blank line still appears where the value would be if it existed. How can I alter my report so that this blank line is eliminated and the remaining fields all move up the row.

For example:

Instead of this when there is no call number:

Location

Online Availability
Subject

I want it to look like this:

Location
Online Availability
Subject

I have a second question. Sometimes the location field says 'Ref', sometimes it says 'Circ'. In my report only, I want the location field to be empty (and not leave a blank line) if the value is 'Circ'.

Any help would be much appreciated!!

 

[WayneRyan]

Lucretia,

You have a couple of ways you can go here.

1st, you can write a very complex IIf statement, or use some VBA code.

Make a textbox on your report with "CanGrow" and "CanShrink".

Then, make a new field in the query that feeds your report:

NewField: FormatStuff([Location], [CallNumber], [OnlineAvailability], [Subject])

Then, put a function in a Public Module:

Public Function FormatStuff(Location As String, _
                            CallNumber As String, _
                            OnlineAvailability As String, _
                            Subject] As String) As String
Dim Temp As String
Temp = ""
If Len(Location) > 0 Then
   Temp = Temp & Location & vbCrLf
End If
If Len(CallNumber) > 0 Then
   Temp = Temp & CallNumber & vbCrLf
End If
If Len(OnlineAvailability) > 0 Then
   Temp = Temp & OnlineAvailability & vbCrLf
End If
If Len(Subject) > 0 Then
   Temp = Temp & Subject & vbCrLf
End If

FormatStuff = Mid(Temp, 1, Len(Temp) - 2)

End Function

Hopefully, any and all "missing" entries will collapse.

Wayne

 

[stopher]

We can simplify Wayne's solution and avoid the need for a funtion.

Create a text box with a height equivalent to 1 line.

Set the can grow property to Yes.

Put the following in as the control source:

=[Location]+(Chr(13) & Chr(10)) & [callnumber]+(Chr(13) & Chr(10)) & [OnlineAvailability]+(Chr(13) & Chr(10)) & [subject]

The use of + instead of & takes care of gettting rid of blank lines.

hth
Chris

 

[Lucretia]

Thank you!

Thank you both so much for your help. I tried stopher's solution and it worked like a charm I think I even have the slightest idea why

Is there anyway to solve my second question about not having 'Circ' appear in the location field and only having 'Ref' appear?

Any further help would be much appreciated!!!