How to Print Report Footer based on Value of Control in Detail

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

I'm new to anything other than simple reports, but this
seems it should be easy, so here goes.

I am showing data per hour, from a Database, where each
hour (08:00, 09:00 to 22:00, 23:00) should be displayed in
the Detail section and the 23:59 value in Hour means the
average for the whole day, and should be displayed in the
Footer section.

This is what I did:
Private Sub GroupFooter1_Print(Cancel As Integer,
PrintCount As Integer)

If [Hour] = "23:59"
Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub

I get all my hour data (every hour) including 23:59, in
the detail section and don't get a footer section at all.
I feel I'm so close, but after a few hours tyring all
sorts of combinations, I am asking for help.

Many Thanks,
Sara
 
sara said:
I'm new to anything other than simple reports, but this
seems it should be easy, so here goes.

I am showing data per hour, from a Database, where each
hour (08:00, 09:00 to 22:00, 23:00) should be displayed in
the Detail section and the 23:59 value in Hour means the
average for the whole day, and should be displayed in the
Footer section.

This is what I did:
Private Sub GroupFooter1_Print(Cancel As Integer,
PrintCount As Integer)

If [Hour] = "23:59"
Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub

I get all my hour data (every hour) including 23:59, in
the detail section and don't get a footer section at all.
I feel I'm so close, but after a few hours tyring all
sorts of combinations, I am asking for help.
Click to expand...


This is a bit tricky since you don't want one of the details
to appear in the detail section. Fortunately, the footer
section does have access to the last detail's date so, if
you want to display any of those fields, you can easily
refer to them the same way you would in the detail section.

I think all you're asking for is the ability to suppress the
detail with the time value of 23:59. This is easily done
with:

If [Hour] = "23:59" Then Cancel = True

There is no need to make the footer invisible since it only
appears once, after the details.
 
Thanks for the quick reply. I tried this - putting this
code in the On Format event of the Detail, but it still
prints all details, including 23:59. I also do want to
print the 23:59 in the footer, as the only detail in the
footer (I'd not display "23:59" but put "Day" or
something).

I changed the code to try to suppress the 11:00 line, just
to see if it works, and I still get all detail lines. The
data is:
Hour field in the table is formatted "Date/Time"
and "Short Time"
On the report, I say the Hour field is formatted
as "Medium Time", so I can display AM and PM for the
users. I find it hard to believe this is the problem, but
thought I'd mention it.

Also, I get this data from an outsource relationship. I
can change the format of the incoming data, if there is
something you would suggest - I could try it. I get the
data as a .csv and have Users import it into Access and
run the report.

Thanks again,
Sara

-----Original Message-----
sara said:
I'm new to anything other than simple reports, but this
seems it should be easy, so here goes.

I am showing data per hour, from a Database, where each
hour (08:00, 09:00 to 22:00, 23:00) should be displayed in
the Detail section and the 23:59 value in Hour means the
average for the whole day, and should be displayed in the
Footer section.

This is what I did:
Private Sub GroupFooter1_Print(Cancel As Integer,
PrintCount As Integer)

If [Hour] = "23:59"
Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub

I get all my hour data (every hour) including 23:59, in
the detail section and don't get a footer section at all.
I feel I'm so close, but after a few hours tyring all
sorts of combinations, I am asking for help.
Click to expand...


This is a bit tricky since you don't want one of the details
to appear in the detail section. Fortunately, the footer
section does have access to the last detail's date so, if
you want to display any of those fields, you can easily
refer to them the same way you would in the detail section.

I think all you're asking for is the ability to suppress the
detail with the time value of 23:59. This is easily done
with:

If [Hour] = "23:59" Then Cancel = True

There is no need to make the footer invisible since it only
appears once, after the details.
Click to expand...
 
sara said:
Thanks for the quick reply. I tried this - putting this
code in the On Format event of the Detail, but it still
prints all details, including 23:59. I also do want to
print the 23:59 in the footer, as the only detail in the
footer (I'd not display "23:59" but put "Day" or
something).

I changed the code to try to suppress the 11:00 line, just
to see if it works, and I still get all detail lines. The
data is:
Hour field in the table is formatted "Date/Time"
and "Short Time"
On the report, I say the Hour field is formatted
as "Medium Time", so I can display AM and PM for the
users. I find it hard to believe this is the problem, but
thought I'd mention it.

Also, I get this data from an outsource relationship. I
can change the format of the incoming data, if there is
something you would suggest - I could try it. I get the
data as a .csv and have Users import it into Access and
run the report.
Click to expand...

If the field in the table is a Date/Time field then you
should not be using quotes around the time. Date/Time
literal values are delimited by # signs. Try chnging my
code to:

If [Hour] = #23:59# Then Cancel = True

I'm assuming that the [Hour] field does not have a date
part. If that's not a valid assumption, then you'll have to
isolate the time part of the field. In this case the code
would be:

If TimeValue([Hour]) = #23:59# Then Cancel = True
--
Marsh
MVP [MS Access]



-----Original Message-----
sara said:
I'm new to anything other than simple reports, but this
seems it should be easy, so here goes.

I am showing data per hour, from a Database, where each
hour (08:00, 09:00 to 22:00, 23:00) should be displayed in
the Detail section and the 23:59 value in Hour means the
average for the whole day, and should be displayed in the
Footer section.

This is what I did:
Private Sub GroupFooter1_Print(Cancel As Integer,
PrintCount As Integer)

If [Hour] = "23:59"
Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub

I get all my hour data (every hour) including 23:59, in
the detail section and don't get a footer section at all.
I feel I'm so close, but after a few hours tyring all
sorts of combinations, I am asking for help.
Click to expand...


This is a bit tricky since you don't want one of the details
to appear in the detail section. Fortunately, the footer
section does have access to the last detail's date so, if
you want to display any of those fields, you can easily
refer to them the same way you would in the detail
section.

I think all you're asking for is the ability to suppress the
detail with the time value of 23:59. This is easily done
with:

If [Hour] = "23:59" Then Cancel = True

There is no need to make the footer invisible since it
only appears once, after the details.
Click to expand...
Click to expand...
 
Thanks! I now have the proper data in the Detail section,
but am not getting a Footer section at all. It is in the
Footer that I want to show the 23:59 record.

I tried the same code with Cancel = False
I tried putting the code on the Print function
All no luck.

If [Hour] <> #23:59# Then
Cancel = True
End If

(By the way, Access converted 23:59 to 11:59 PM, which is
fine for me)

I modified my other formatting code (from anoter working
report)
If [Hour] = #11:59:00 PM# Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

Is getting the 23:59 (summary record) only to display in
the footer possible? Alternatively, I can pull the data
out in queries, but I feel like I should be able to
manipulate the footer this way.

Thanks
-----Original Message-----
sara said:
Thanks for the quick reply. I tried this - putting this
code in the On Format event of the Detail, but it still
prints all details, including 23:59. I also do want to
print the 23:59 in the footer, as the only detail in the
footer (I'd not display "23:59" but put "Day" or
something).

I changed the code to try to suppress the 11:00 line, just
to see if it works, and I still get all detail lines. The
data is:
Hour field in the table is formatted "Date/Time"
and "Short Time"
On the report, I say the Hour field is formatted
as "Medium Time", so I can display AM and PM for the
users. I find it hard to believe this is the problem, but
thought I'd mention it.

Also, I get this data from an outsource relationship. I
can change the format of the incoming data, if there is
something you would suggest - I could try it. I get the
data as a .csv and have Users import it into Access and
run the report.
Click to expand...

If the field in the table is a Date/Time field then you
should not be using quotes around the time. Date/Time
literal values are delimited by # signs. Try chnging my
code to:

If [Hour] = #23:59# Then Cancel = True

I'm assuming that the [Hour] field does not have a date
part. If that's not a valid assumption, then you'll have to
isolate the time part of the field. In this case the code
would be:

If TimeValue([Hour]) = #23:59# Then Cancel = True
--
Marsh
MVP [MS Access]



-----Original Message-----
sara wrote:

I'm new to anything other than simple reports, but this
seems it should be easy, so here goes.

I am showing data per hour, from a Database, where each
hour (08:00, 09:00 to 22:00, 23:00) should be
Click to expand...
displayed
in
the Detail section and the 23:59 value in Hour means the
average for the whole day, and should be displayed in the
Footer section.

This is what I did:
Private Sub GroupFooter1_Print(Cancel As Integer,
PrintCount As Integer)

If [Hour] = "23:59"
Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub

I get all my hour data (every hour) including 23:59, in
the detail section and don't get a footer section at all.
I feel I'm so close, but after a few hours tyring all
sorts of combinations, I am asking for help.


This is a bit tricky since you don't want one of the details
to appear in the detail section. Fortunately, the footer
section does have access to the last detail's date so, if
you want to display any of those fields, you can easily
refer to them the same way you would in the detail
section.

I think all you're asking for is the ability to
Click to expand...
suppress
the
detail with the time value of 23:59. This is easily done
with:

If [Hour] = "23:59" Then Cancel = True

There is no need to make the footer invisible since it
only appears once, after the details.
Click to expand...
Click to expand...
.
Click to expand...
 
Sara said:
Thanks! I now have the proper data in the Detail section,
but am not getting a Footer section at all. It is in the
Footer that I want to show the 23:59 record.
Click to expand...


Make sure that the footer section has its Visible property
set to Yes and that you do not have any code that changes
its Visible to False.

As I said before, as long as the 23:59 record is the last
record in the report's record source query, you do not have
to do anything special to display its fields in the footer
section.
 
I am so sorry to bother you on this, and I don't want it
to appear that I am not appreciative of all the help so
far. But the report still isn't where I need it to be.

The 23:59 record is the last record, per "group". We are
retail with 15 stores - we have one set of records per
store, and one "store number 99" for the whole chain.
Each group ends with a 23:59 record with no date.
I put the code in the Footer On Format, with <>#23:59# and
the only record I get in the footer is the repeat of the
8:00 record - the first record in the group.
What am I missing?
 
I am so sorry to bother you on this, and I don't want it
to appear that I am not appreciative of all the help so
far. But the report still isn't where I need it to be.

The 23:59 record is the last record, per "group". We are
retail with 15 stores - we have one set of records per
store, and one "store number 99" for the whole chain.
Each group ends with a 23:59 record with no date.
I put the code in the Footer On Format, with <>#23:59# and
the only record I get in the footer is the repeat of the
8:00 record - the first record in the group.
What am I missing?
Click to expand...

You have no need to apologize. I'm the one that's causing
all the trouble here. I finally called a time-out on myself
to reconstruct your scenario and, without a doubt, I was
giving you the wrong info about binding group footer
controls to the data in the last record of the group (as you
say, it's the first, not last, data that's used). I was
misremembering something about page footers that has nothing
to do with your situation..

If you'll please forgive me, let's try this instead. Make
the controls in the group footer unbound and add code to the
Detail section's Format event to copy the values from the
23:59 record to the footer.

If [Hour] = #23:59# Then
Cancel = True
footerbox1 = detailbox1
footerbox2 = detailbox2
. . .
End If

As far as this objective is concerned, you do not need any
code in the group footer's format event.
--
Marsh
MVP [MS Access]



 
IT WORKS!!! You're a genius!!!

This is fantastic. Amazing how three little lines of code
can make you feel so good.

Thanks again -
Until next time (as I'm sure there will be another
question sometime)
Sara
-----Original Message-----
I am so sorry to bother you on this, and I don't want it
to appear that I am not appreciative of all the help so
far. But the report still isn't where I need it to be.

The 23:59 record is the last record, per "group". We are
retail with 15 stores - we have one set of records per
store, and one "store number 99" for the whole chain.
Each group ends with a 23:59 record with no date.
I put the code in the Footer On Format, with <>#23:59# and
the only record I get in the footer is the repeat of the
8:00 record - the first record in the group.
What am I missing?
Click to expand...

You have no need to apologize. I'm the one that's causing
all the trouble here. I finally called a time-out on myself
to reconstruct your scenario and, without a doubt, I was
giving you the wrong info about binding group footer
controls to the data in the last record of the group (as you
say, it's the first, not last, data that's used). I was
misremembering something about page footers that has nothing
to do with your situation..

If you'll please forgive me, let's try this instead. Make
the controls in the group footer unbound and add code to the
Detail section's Format event to copy the values from the
23:59 record to the footer.

If [Hour] = #23:59# Then
Cancel = True
footerbox1 = detailbox1
footerbox2 = detailbox2
. . .
End If

As far as this objective is concerned, you do not need any
code in the group footer's format event.
--
Marsh
MVP [MS Access]




.
Click to expand...
 
sara said:
IT WORKS!!! You're a genius!!!
Click to expand...

That's very good news, but let's not go too far overboard
with the praise (it is appreciated but unwarrented).
This is fantastic. Amazing how three little lines of code
can make you feel so good.
Click to expand...

I know exactly what you mean. Some of us "problem solver"
type people "just love it when a plan comes together",
especially when it only takes a couple lines of code.

OK, the party's over, time to get to work on the next
problem ;-)
--
Marsh
MVP [MS Access]

-----Original Message-----
I am so sorry to bother you on this, and I don't want it
to appear that I am not appreciative of all the help so
far. But the report still isn't where I need it to be.

The 23:59 record is the last record, per "group". We are
retail with 15 stores - we have one set of records per
store, and one "store number 99" for the whole chain.
Each group ends with a 23:59 record with no date.
I put the code in the Footer On Format, with <>#23:59# and
the only record I get in the footer is the repeat of the
8:00 record - the first record in the group.
What am I missing?
Click to expand...

You have no need to apologize. I'm the one that's causing
all the trouble here. I finally called a time-out on myself
to reconstruct your scenario and, without a doubt, I was
giving you the wrong info about binding group footer
controls to the data in the last record of the group (as you
say, it's the first, not last, data that's used). I was
misremembering something about page footers that has nothing
to do with your situation..

If you'll please forgive me, let's try this instead. Make
the controls in the group footer unbound and add code to the
Detail section's Format event to copy the values from the
23:59 record to the footer.

If [Hour] = #23:59# Then
Cancel = True
footerbox1 = detailbox1
footerbox2 = detailbox2
. . .
End If

As far as this objective is concerned, you do not need any
code in the group footer's format event.
--
Marsh
MVP [MS Access]



-----Original Message-----
Sara wrote:

Thanks! I now have the proper data in the Detail
section,
but am not getting a Footer section at all. It is in
the
Footer that I want to show the 23:59 record.


Make sure that the footer section has its Visible property
set to Yes and that you do not have any code that changes
its Visible to False.

As I said before, as long as the 23:59 record is the last
record in the report's record source query, you do not
have
to do anything special to display its fields in the footer
section.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top