Conditional Footers in a report

[FSHOTT]

Can someone explain how I can create an expression(I assume and IIF()) that
will put a footer in a report if true and not put it in if false?

 

[Klatuu]

It is not a matter of either putting a footer in or not putting a footer in a
report. That has to be done at design time. It is possble to open a report
in design view and manipulate the design using VBA, but I strongly advise
against it. If you encounter an error, you could corrupt your report. Also,
if you ever deliver and mde or have any users that use the Runtime version of
Access, it will not work.

The correct approach is to use the Formatevens of the footer and make the
footer either Visible or Not Visible.

 

[FSHOTT]

I assume you mean Formatevents. How do I do this?

 

[Klatuu]

No, I mean the Footer Format Event
You will see it in the properties dialog as On Format

Click on the small command button to the right of the text box for the
event. The VBA editor will open. That is where you write the logic to make
the footer either visible or not.

 

[FSHOTT]

I am somewhat new to using Visual Basic and having trouble implementing your
recomendation. Can you provide a example?

 

[Klatuu]

Describe the rules for determining whether the footer should be visible or not.
Is this a page footer or a report footer?

 

[FSHOTT]

It is a report footer based on a "CableID" parameter. If the CableID is blank
I would like to eliminate the footer (make it 0 height and/or make the
current cross hatch seperator not vivible.

 

[Klatuu]

Okay, not a problem. You just make the footer Visible property False, but I
don't understand what you mean by parameter. Is it a field in the report's
recordset, a control on a form, or what. Basically, I need a definition of
what and wereh CableID is.

 

[FSHOTT]

Yes CableID is a field in the reports record set.

 

[Klatuu]

Okay, thanks for the info:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![CableID], vbNullString)) > 0
End Sub

 

[FSHOTT]

Klatuu I tried your solution but it did not work the way I am hoping it to
work in a report. My objective is for a text box to be displayed in a
"CableID footer" when the CableID field is text(a non blank string) and No
"CableID footer" be displayed when the CableID field is blank. The solution
you gave me did not appear to work this way. Perhaps I did not implement it
correctly.

 

[Klatuu]

Can you post back with the code the way you implemented it?

 

[FSHOTT]

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

 

[Klatuu]

You either have the code in the wrong event or you are trying to hid the
wrong section. The code is in a Group footer section. If you want to make
the group footer hidden, change it to:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter3.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

If you are wanting to hide the report footer, move it the the report
footer's format event.

I am always happy to assist with any database needs; however, free
assistance is limited to these public news groups so all may benefit. If you
feel you need professional assistance, please post back with an email address
and I will contact you to discuss rates and terms.

--
Dave Hargis, Microsoft Access MVP

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub
--
frank-a

Can you post back with the code the way you implemented it?

 

[FSHOTT]

OK! That worked Thanks = . I thought I tried the code you sent and it
didn't work. I obviously didn't do it correctly. By the way I have been
trying to take a 2 day VBA Course for ACCESS locally but the instructor left
the organization. Can you direct me to sources for courses in ACCESS and VBA
for ACCESS? Near upstate NY?
--
frank-a

You either have the code in the wrong event or you are trying to hid the
wrong section. The code is in a Group footer section. If you want to make
the group footer hidden, change it to:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter3.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

If you are wanting to hide the report footer, move it the the report
footer's format event.

I am always happy to assist with any database needs; however, free
assistance is limited to these public news groups so all may benefit. If you
feel you need professional assistance, please post back with an email address
and I will contact you to discuss rates and terms.

--
Dave Hargis, Microsoft Access MVP

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub
--
frank-a

Can you post back with the code the way you implemented it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu I tried your solution but it did not work the way I am hoping it to
work in a report. My objective is for a text box to be displayed in a
"CableID footer" when the CableID field is text(a non blank string) and No
"CableID footer" be displayed when the CableID field is blank. The solution
you gave me did not appear to work this way. Perhaps I did not implement it
correctly.

 

[Klatuu]

I wouldn't know of any specific trainers available in your area, but here is
a link to a good primer that may help:

http://www.accessmvp.com/Strive4Peace/Index.htm

If you are one (like I) who gets more from classroom participation, you
might check you local community college. They will sometimes have Access
courses. Also, just some intensive googling might be in order.
--
Dave Hargis, Microsoft Access MVP

OK! That worked Thanks = . I thought I tried the code you sent and it
didn't work. I obviously didn't do it correctly. By the way I have been
trying to take a 2 day VBA Course for ACCESS locally but the instructor left
the organization. Can you direct me to sources for courses in ACCESS and VBA
for ACCESS? Near upstate NY?
--
frank-a

You either have the code in the wrong event or you are trying to hid the
wrong section. The code is in a Group footer section. If you want to make
the group footer hidden, change it to:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter3.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

If you are wanting to hide the report footer, move it the the report
footer's format event.

I am always happy to assist with any database needs; however, free
assistance is limited to these public news groups so all may benefit. If you
feel you need professional assistance, please post back with an email address
and I will contact you to discuss rates and terms.

--
Dave Hargis, Microsoft Access MVP

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub
--
frank-a


:

Can you post back with the code the way you implemented it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu I tried your solution but it did not work the way I am hoping it to
work in a report. My objective is for a text box to be displayed in a
"CableID footer" when the CableID field is text(a non blank string) and No
"CableID footer" be displayed when the CableID field is blank. The solution
you gave me did not appear to work this way. Perhaps I did not implement it
correctly.

 

[FSHOTT]

Dave Thank You for the leads.
--
frank-a

I wouldn't know of any specific trainers available in your area, but here is
a link to a good primer that may help:

http://www.accessmvp.com/Strive4Peace/Index.htm

If you are one (like I) who gets more from classroom participation, you
might check you local community college. They will sometimes have Access
courses. Also, just some intensive googling might be in order.
--
Dave Hargis, Microsoft Access MVP

OK! That worked Thanks = . I thought I tried the code you sent and it
didn't work. I obviously didn't do it correctly. By the way I have been
trying to take a 2 day VBA Course for ACCESS locally but the instructor left
the organization. Can you direct me to sources for courses in ACCESS and VBA
for ACCESS? Near upstate NY?
--
frank-a

You either have the code in the wrong event or you are trying to hid the
wrong section. The code is in a Group footer section. If you want to make
the group footer hidden, change it to:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter3.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

If you are wanting to hide the report footer, move it the the report
footer's format event.

I am always happy to assist with any database needs; however, free
assistance is limited to these public news groups so all may benefit. If you
feel you need professional assistance, please post back with an email address
and I will contact you to discuss rates and terms.

--
Dave Hargis, Microsoft Access MVP


:

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub
--
frank-a


:

Can you post back with the code the way you implemented it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu I tried your solution but it did not work the way I am hoping it to
work in a report. My objective is for a text box to be displayed in a
"CableID footer" when the CableID field is text(a non blank string) and No
"CableID footer" be displayed when the CableID field is blank. The solution
you gave me did not appear to work this way. Perhaps I did not implement it
correctly.

 

[FSHOTT]

Dave I have an additional question since I implenmented your solution. Now
that I can make the footer visible or invisible based on a [WireID] field, I
would now like to make another footer visible based on whether a [DZone]
field from the current and previous records are equal or changed. I tried to
define a [DZone1] and [Dzone2] and compare them to determine whether the
footer is visible but it appears my DZones are always equal. Is there a way I
can get DZone1 = [DZone].[for record x-1] and DZone2 = [DZone].[for record
x]? Then I assume I can use a logical expression to set the visibility of a
footer.
--
frank-a

Dave Thank You for the leads.
--
frank-a

I wouldn't know of any specific trainers available in your area, but here is
a link to a good primer that may help:

http://www.accessmvp.com/Strive4Peace/Index.htm

If you are one (like I) who gets more from classroom participation, you
might check you local community college. They will sometimes have Access
courses. Also, just some intensive googling might be in order.
--
Dave Hargis, Microsoft Access MVP

OK! That worked Thanks = . I thought I tried the code you sent and it
didn't work. I obviously didn't do it correctly. By the way I have been
trying to take a 2 day VBA Course for ACCESS locally but the instructor left
the organization. Can you direct me to sources for courses in ACCESS and VBA
for ACCESS? Near upstate NY?
--
frank-a


:

You either have the code in the wrong event or you are trying to hid the
wrong section. The code is in a Group footer section. If you want to make
the group footer hidden, change it to:
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter3.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub

If you are wanting to hide the report footer, move it the the report
footer's format event.

I am always happy to assist with any database needs; however, free
assistance is limited to these public news groups so all may benefit. If you
feel you need professional assistance, please post back with an email address
and I will contact you to discuss rates and terms.

--
Dave Hargis, Microsoft Access MVP


:

Here is the code. Note that the database I am trying this in uses Wire ID
instead of CableID. Same field just renamed. Is there a way I can send you
the database to look at?


Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Me.ReportFooter.Visible = Len(Nz(Me![Wire ID], vbNullString)) > 0
End Sub
--
frank-a


:

Can you post back with the code the way you implemented it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu I tried your solution but it did not work the way I am hoping it to
work in a report. My objective is for a text box to be displayed in a
"CableID footer" when the CableID field is text(a non blank string) and No
"CableID footer" be displayed when the CableID field is blank. The solution
you gave me did not appear to work this way. Perhaps I did not implement it
correctly.