Exclude Text Fields from Report

[irunergoiam]

I'm trying to write a report (based on a single query - all the data fields are there) that will serve as an Employment Offer Letter. Many of the fields all offer letters will have in common. However, there are some fields that do not apply to all newly hired employees. For example, not all recipients of the offer letter will receive a [SignOn] bonus, [Relocation] assistance, and/or additional hours of [PTO] (paid time off) upon hire.

For these fields, I am concatenating text and inserting the data field in the text field.

What I'm after is a way to supress (make invisible) the entire concatenated text field where one or more of the data fields being concatenated with the text is null. I would then like it to fill in the gap created by the hidden field with the next concatenated text field without null value data fields.

A friend suggested:

If Me.PTO Is NullThen
Me.PTO.Visible = False
Else
Me.PTO.Visible = True
If Me.Relocation Is NullThen
Me.Relocation.Visible = False
Else
Me.Relocation.Visible = True
End If

As an On Format Event in the Details section (where all the fields are located) of the report.

Thanks in advance for any insights. This is by far the most complex thing I've tried doing in reports.

 

[John Big Booty]

Try;

If IsNull(Me.PTO) Then
     Me.PTO.Visible = False
Else
     Me.PTO.Visible = True
If IsNull(Me.Relocation) Then
     Me.Relocation.Visible = False
Else
     Me.Relocation.Visible = True
End If

 

[SOS]

Try;

If IsNull(Me.PTO) Then
     Me.PTO.Visible = False
Else
     Me.PTO.Visible = True
If IsNull(Me.Relocation) Then
     Me.Relocation.Visible = False
Else
     Me.Relocation.Visible = True
End If

Or simplified:

     Me.PTO.Visible = (Not IsNull(Me.PTO))
     Me.Relocation.Visible = (Not IsNull(Me.Relocation))

 

[irunergoiam]

Thanks John Big Booty. I tried your suggestion:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.PTO) Then
Me.PTO.Visible = False
Else
Me.PTO.Visible = True
If IsNull(Me.Relocation) Then
Me.Relocation.Visible = False
Else
Me.Relocation.Visible = True
End IfIf IsNull(Me.PTO) Then
Me.PTO.Visible = False
Else
Me.PTO.Visible = True
If IsNull(Me.Relocation) Then
Me.Relocation.Visible = False
Else
Me.Relocation.Visible = True
End If
End Sub

And found a couple of things:
1) I get the following error - "Compile error: Method or data member not found". I found that adding the data fields by themselves (in addition to including them in the concatenated text fileld), I no longer get the error.

2) "Compile error: Block If without End If" which I fixed by adding another End If before End Sub

3) The concatenated text/data field still shows up with the text and blanks where the data fields are null for the record.
Examples of concatenated text/data fields in design view:

="As part of your compensation package, all out of pocket COBRA costs are eligible for reimbursement up to a maximum of " & [COBRA] & "."

="You will also receive relocation assistance of " & [Relocation] & ". You will be contacted by our relocation vendor to discuss relocation options available to you."

The results in Layout Preview still show text where data field is blank:

As part of your compensation package, all out of pocket COBRA costs are eligible for reimbursement up to a maximum of . You will also receive relocation assistance of $5,000.00. You will be contacted by our relocation vendor to discuss relocation options available to you.

In this instant, what I would like would be for the sentence (text field) with the COBRA data field to be supressed and have only the next sentence (text field) be moved up and displayed in its place on the page as follows:

You will also receive relocation assistance of $5,000.00. You will be contacted by our relocation vendor to discuss relocation options available to you.

There would also be instances where I would want the next field not to move up (for example, when that next sentence is the start of a new paragraph).

I'm sure Access can handle this (so far the "error" is taking place somewhere between my chair and the keyboard!), I just need some guidance. Much thanks...

 

[John Big Booty]

Try the following;

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.PTO) Then
     Me.PTO.Visible = False
Else
     Me.PTO.Visible = True
End If

If IsNull(Me.Relocation) Then
     Me.Relocation.Visible = False
Else
     Me.Relocation.Visible = True
End If

If IsNull(Me.PTO) Then
     Me.PTO.Visible = False
Else
     Me.PTO.Visible = True
End If

If IsNull(Me.Relocation) Then
     Me.Relocation.Visible = False
Else
     Me.Relocation.Visible = True
End If

End Sub

You were missing a couple of End If's, and you had an If statement on the same line as an End If

 

[SOS]

Just a point of information -

For every IF statement there, you can reduce it to just one line. Again, as I said -

This:

If IsNull(Me.PTO) Then
     Me.PTO.Visible = False
Else
     Me.PTO.Visible = True
End If

does EXACTLY the same thing as:

Me.PTO.Visible = (Not IsNull(Me.PTO))

But is only ONE line instead of 5 lines of code.

 

[irunergoiam]

Thanks John Big Booty and SOS!! I'm just cutting my teeth with this whole coding thing, but like the world that is opening up to me in Access with it!

-Andrew

 

[irunergoiam]

Hmmmm... While the code you provided works great in hiding the null fields from the report, I am wondering if there is a way to move a non-null field up to follow the preceeding non-null field - as where there are null fields, there are gaps (the fields are only hidden) in the print view of the report.

e.g.,

You will also receive additional compensation as follows:
*40 additional hours of Paid Time Off (PTO)
*Reimbursement of COBRA expenses, up to a maximum of $2,500.00

This works great when there are no null fields for the PTO and COBRA fields, but when the PTO field is null it shows up as:

You will also receive additional compensation as follows:

*Reimbursement of COBRA expenses, up to a maximum of $2,500.00

This distorting the bullet formatting. Now, I'm not married to using bullets.

In fact, I've got a field that pulls in any Not-Null "AdditionalCompDetails", but I run into the same problem as most new employees who will not receive any "AdditionalCompDetails. In those cases (where [AdditionalCompDetails] is null), I would like to move all of the text up to fill in the gap created by this Null paragraph.

Any ideas????

 

[bohemian9485]

You can move the visible field up to cover the blank left by the field that was made invisible by using visibleField.Top = invisibleField.Top in your code