Programming for footer section

L

Linda Brown

(Using Access 2000 on Windows XP.) I have a report tied to a table. In the
report of the GroupFooter1 section, I need to add code to either the OnFormat
or OnPrint event, I think.

Qty is being added based on a weekly total. I then need to use IF
statements to get the right figure to multiply the total within that week. I
cannot get it straight on what I need to do, and really need some expert help.

Here is the coding for the OnFormat event of the RefDate Footer (the totals
are grouped on the week):
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

WeekTot is what I have titled the field that will add the Qty field from the
table, and those are calculating correctly inside the group footer.

The unbound control titled WeekPrice is what I am getting to come up;
however, here are the results of the report (note that the numbers in
parentheses should be the multiplier, but the second result for 663 is
INCORRECT and has actually been multiplied by 5.25 and not 5.1):

5 (7.5) = 37.50
663 (5.1) = 3480.75*
544 (5.25) = 2856

Can someone help me figure out what I have done wrong? I know this must be
an error on my part, and I am still learning about VBA. I always use DAO
when I can.

Thank you. --Linda
 
L

Linda Brown

P.S. The report footer is not summing the Invoice Total. The expression for
that field is: =(Sum([WeekPrice]))

Again, thank you for any assistance. --Linda
 
K

Ken Snell [MVP]

Your VBA syntax is wrong; try this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 And Me.WeekTot < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 And Me.WeekTot < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

Personally, I'd use a Select Case:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.WeekTot
Case Is <= 330
Me.WeekPrice = Me.WeekTot * 7.5
Case Is < 620
Me.WeekPrice = Me.WeekTot * 5.25
Case Is < 744
Me.WeekPrice = Me.WeekTot * 5.1
Case Else
Me.WeekPrice = Me.WeekTot * 4.9
End Select
End Sub


Regarding your report footer not summing correctly -- that is because
WeekPrice is not a field in the report's recordsource. Sum works on the
recordset's fields, not on unbound controls on the report. You'll need to
use a different method to get the sum -- perhaps you may have to put the
above calculations into the report's recordsource query as a calculated
field and then you can bind a control to it for display and then you can sum
it in the report's footer section. Alternatively, you could use code to read
through the report's entire recordsource's records and recalculate the
individual WeekPrice values and then sum them in the code, then write that
value to the report's control in the report footer section.

I'd personally try to do the calculation in the query and bind a control to
that calculated field.
 
L

Linda Brown

Thank you, Ken. I knew the first part was something very simple.

As for the second part in a query, I could not figure out the syntax to get
the total for the date period to group them in a week's time in the query. I
knew the report could automatically calculate the sums of the quantities for
me in a week's time. The field is RefDate and each is a single date with
each record. (With each date, a certain amount of pallets are worked; that
is where the other information came from.)

Any suggestion on how to get the date to group in the query? Thanks.
--Linda
 
K

Ken Snell [MVP]

If I'm understanding your question correctly, you could open Sorting &
Grouping, and have a group that is based on this:
DatePart("ww",[NameOfDateField])
Select the "Group Footer" for this group, and put the Sum textbox in it to
sum the desired values for that week.

--

Ken Snell
<MS ACCESS MVP>
 
L

Linda Brown

Actually, I put the following inside the query as you suggested:
Weekly: DatePart("ww",[RefDate])
I then get three sets of records: Week 32, 33, 34.

What I am wanting to do is sum the qty column based on the groupings of
weekly. In the report I have to show each record because it is an invoice
that will be going to a customer. That is where each grouping for the week
has to show the total amount for the week, based on the totals we previously
discussed.

Thus, I went back to the query on which to base the report on rather than on
the table, as you suggested. I cannot figure how to use Sum or DSum to get
the total dollar amounts for the entire week. (For instance, there is only
one entry in Week 32 and numerous entries in Weeks 33 and 34.) Here is the
query result:
RefDate Weekly BolNo Qty Hot Shot OurTruck
05-Aug-05 32 32152 5 N N
10-Aug-05 33 34930 60 N N
10-Aug-05 33 34929 1 N N
11-Aug-05 33 32451 1 N N
13-Aug-05 33 35182 1 N N
12-Aug-05 33 34934 60 N N
13-Aug-05 33 35184 60 N N
08-Aug-05 33 35277 60 N N
11-Aug-05 33 34932 60 N N
12-Aug-05 33 34935 60 N N
13-Aug-05 33 35181 60 N N
09-Aug-05 33 35131 60 N N
08-Aug-05 33 34927 60 N N
10-Aug-05 33 34928 59 N N
11-Aug-05 33 34931 60 N N
12-Aug-05 33 35179 1 N N
17-Aug-05 34 37385 1 Y Y
17-Aug-05 34 37393 60 N N
18-Aug-05 34 37398 61 N N
18-Aug-05 34 37413 60 N N
19-Aug-05 34 37422 59 N N
15-Aug-05 34 35187 60 N N
17-Aug-05 34 34936 60 N N
16-Aug-05 34 37361 1 Y N
15-Aug-05 34 31626 60 N N
15-Aug-05 34 31627 1 N N
16-Aug-05 34 37350 60 N N
16-Aug-05 34 37368 60 N N
18-Aug-05 34 37400 1 N N

Thus, $ amounts for Week 32 calculate to $37.50 (5 * 7.5); Week 33 $
amounts calculate to $3381.30 (663 * 5.1); and Week 34 $ amounts calculate to
$2856 (544 * 5.25). The entire report will have to add these three groups
along with the number of Hot Shots * $50 plus the number of Our Truck * $50.

I am so sorry that I keep having to ask for help, but I really need the
answer before I have to send out the invoice on Monday. And I really do
appreciate your help. --Linda

Ken Snell said:
If I'm understanding your question correctly, you could open Sorting &
Grouping, and have a group that is based on this:
DatePart("ww",[NameOfDateField])
Select the "Group Footer" for this group, and put the Sum textbox in it to
sum the desired values for that week.

--

Ken Snell
<MS ACCESS MVP>



Linda Brown said:
Thank you, Ken. I knew the first part was something very simple.

As for the second part in a query, I could not figure out the syntax to
get
the total for the date period to group them in a week's time in the query.
I
knew the report could automatically calculate the sums of the quantities
for
me in a week's time. The field is RefDate and each is a single date with
each record. (With each date, a certain amount of pallets are worked;
that
is where the other information came from.)

Any suggestion on how to get the date to group in the query? Thanks.
--Linda
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

In the report, (View | Sorting & Grouping), add "Weekly" as the first field
for sort/group. Tell the report to have a group header/footer for it. Put a
textbox in its group footer section that will sum the desired quantity for
the desired field.

--

Ken Snell
<MS ACCESS MVP>

Linda Brown said:
Actually, I put the following inside the query as you suggested:
Weekly: DatePart("ww",[RefDate])
I then get three sets of records: Week 32, 33, 34.

What I am wanting to do is sum the qty column based on the groupings of
weekly. In the report I have to show each record because it is an invoice
that will be going to a customer. That is where each grouping for the
week
has to show the total amount for the week, based on the totals we
previously
discussed.

Thus, I went back to the query on which to base the report on rather than
on
the table, as you suggested. I cannot figure how to use Sum or DSum to
get
the total dollar amounts for the entire week. (For instance, there is
only
one entry in Week 32 and numerous entries in Weeks 33 and 34.) Here is
the
query result:
RefDate Weekly BolNo Qty Hot Shot OurTruck
05-Aug-05 32 32152 5 N N
10-Aug-05 33 34930 60 N N
10-Aug-05 33 34929 1 N N
11-Aug-05 33 32451 1 N N
13-Aug-05 33 35182 1 N N
12-Aug-05 33 34934 60 N N
13-Aug-05 33 35184 60 N N
08-Aug-05 33 35277 60 N N
11-Aug-05 33 34932 60 N N
12-Aug-05 33 34935 60 N N
13-Aug-05 33 35181 60 N N
09-Aug-05 33 35131 60 N N
08-Aug-05 33 34927 60 N N
10-Aug-05 33 34928 59 N N
11-Aug-05 33 34931 60 N N
12-Aug-05 33 35179 1 N N
17-Aug-05 34 37385 1 Y Y
17-Aug-05 34 37393 60 N N
18-Aug-05 34 37398 61 N N
18-Aug-05 34 37413 60 N N
19-Aug-05 34 37422 59 N N
15-Aug-05 34 35187 60 N N
17-Aug-05 34 34936 60 N N
16-Aug-05 34 37361 1 Y N
15-Aug-05 34 31626 60 N N
15-Aug-05 34 31627 1 N N
16-Aug-05 34 37350 60 N N
16-Aug-05 34 37368 60 N N
18-Aug-05 34 37400 1 N N

Thus, $ amounts for Week 32 calculate to $37.50 (5 * 7.5); Week 33 $
amounts calculate to $3381.30 (663 * 5.1); and Week 34 $ amounts calculate
to
$2856 (544 * 5.25). The entire report will have to add these three groups
along with the number of Hot Shots * $50 plus the number of Our Truck *
$50.

I am so sorry that I keep having to ask for help, but I really need the
answer before I have to send out the invoice on Monday. And I really do
appreciate your help. --Linda

Ken Snell said:
If I'm understanding your question correctly, you could open Sorting &
Grouping, and have a group that is based on this:
DatePart("ww",[NameOfDateField])
Select the "Group Footer" for this group, and put the Sum textbox in it
to
sum the desired values for that week.

--

Ken Snell
<MS ACCESS MVP>



Linda Brown said:
Thank you, Ken. I knew the first part was something very simple.

As for the second part in a query, I could not figure out the syntax to
get
the total for the date period to group them in a week's time in the
query.
I
knew the report could automatically calculate the sums of the
quantities
for
me in a week's time. The field is RefDate and each is a single date
with
each record. (With each date, a certain amount of pallets are worked;
that
is where the other information came from.)

Any suggestion on how to get the date to group in the query? Thanks.
--Linda

:

Your VBA syntax is wrong; try this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 And Me.WeekTot < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 And Me.WeekTot < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

Personally, I'd use a Select Case:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
Select Case Me.WeekTot
Case Is <= 330
Me.WeekPrice = Me.WeekTot * 7.5
Case Is < 620
Me.WeekPrice = Me.WeekTot * 5.25
Case Is < 744
Me.WeekPrice = Me.WeekTot * 5.1
Case Else
Me.WeekPrice = Me.WeekTot * 4.9
End Select
End Sub


Regarding your report footer not summing correctly -- that is because
WeekPrice is not a field in the report's recordsource. Sum works on
the
recordset's fields, not on unbound controls on the report. You'll need
to
use a different method to get the sum -- perhaps you may have to put
the
above calculations into the report's recordsource query as a
calculated
field and then you can bind a control to it for display and then you
can
sum
it in the report's footer section. Alternatively, you could use code
to
read
through the report's entire recordsource's records and recalculate the
individual WeekPrice values and then sum them in the code, then write
that
value to the report's control in the report footer section.

I'd personally try to do the calculation in the query and bind a
control
to
that calculated field.
--

Ken Snell
<MS ACCESS MVP>



(Using Access 2000 on Windows XP.) I have a report tied to a table.
In
the
report of the GroupFooter1 section, I need to add code to either the
OnFormat
or OnPrint event, I think.

Qty is being added based on a weekly total. I then need to use IF
statements to get the right figure to multiply the total within that
week.
I
cannot get it straight on what I need to do, and really need some
expert
help.

Here is the coding for the OnFormat event of the RefDate Footer (the
totals
are grouped on the week):
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

WeekTot is what I have titled the field that will add the Qty field
from
the
table, and those are calculating correctly inside the group footer.

The unbound control titled WeekPrice is what I am getting to come
up;
however, here are the results of the report (note that the numbers
in
parentheses should be the multiplier, but the second result for 663
is
INCORRECT and has actually been multiplied by 5.25 and not 5.1):

5 (7.5) = 37.50
663 (5.1) = 3480.75*
544 (5.25) = 2856

Can someone help me figure out what I have done wrong? I know this
must
be
an error on my part, and I am still learning about VBA. I always
use
DAO
when I can.

Thank you. --Linda
Click to expand...
Click to expand...
Click to expand...
 
L

Linda Brown

Actually, I had already done that. But remember that the section totals had
different variables, depending on the totals that were grouped on weeks. I
did some more searching last night and found Marshall Burton's suggestion was
just what I needed:

If you have to use code to calculate the number values, then
add an invisible text box named txtRunValue to the detail
section, Set its control source to =unboundtextboxname and
its RunningSum property to Over All. The report footer text
box can then display the sum by using the expression
=txtRunValue

I applaud all of you experts. A very warm thank-you to all of you who work
to help us out. --Linda

Ken Snell said:
In the report, (View | Sorting & Grouping), add "Weekly" as the first field
for sort/group. Tell the report to have a group header/footer for it. Put a
textbox in its group footer section that will sum the desired quantity for
the desired field.

--

Ken Snell
<MS ACCESS MVP>

Linda Brown said:
Actually, I put the following inside the query as you suggested:
Weekly: DatePart("ww",[RefDate])
I then get three sets of records: Week 32, 33, 34.

What I am wanting to do is sum the qty column based on the groupings of
weekly. In the report I have to show each record because it is an invoice
that will be going to a customer. That is where each grouping for the
week
has to show the total amount for the week, based on the totals we
previously
discussed.

Thus, I went back to the query on which to base the report on rather than
on
the table, as you suggested. I cannot figure how to use Sum or DSum to
get
the total dollar amounts for the entire week. (For instance, there is
only
one entry in Week 32 and numerous entries in Weeks 33 and 34.) Here is
the
query result:
RefDate Weekly BolNo Qty Hot Shot OurTruck
05-Aug-05 32 32152 5 N N
10-Aug-05 33 34930 60 N N
10-Aug-05 33 34929 1 N N
11-Aug-05 33 32451 1 N N
13-Aug-05 33 35182 1 N N
12-Aug-05 33 34934 60 N N
13-Aug-05 33 35184 60 N N
08-Aug-05 33 35277 60 N N
11-Aug-05 33 34932 60 N N
12-Aug-05 33 34935 60 N N
13-Aug-05 33 35181 60 N N
09-Aug-05 33 35131 60 N N
08-Aug-05 33 34927 60 N N
10-Aug-05 33 34928 59 N N
11-Aug-05 33 34931 60 N N
12-Aug-05 33 35179 1 N N
17-Aug-05 34 37385 1 Y Y
17-Aug-05 34 37393 60 N N
18-Aug-05 34 37398 61 N N
18-Aug-05 34 37413 60 N N
19-Aug-05 34 37422 59 N N
15-Aug-05 34 35187 60 N N
17-Aug-05 34 34936 60 N N
16-Aug-05 34 37361 1 Y N
15-Aug-05 34 31626 60 N N
15-Aug-05 34 31627 1 N N
16-Aug-05 34 37350 60 N N
16-Aug-05 34 37368 60 N N
18-Aug-05 34 37400 1 N N

Thus, $ amounts for Week 32 calculate to $37.50 (5 * 7.5); Week 33 $
amounts calculate to $3381.30 (663 * 5.1); and Week 34 $ amounts calculate
to
$2856 (544 * 5.25). The entire report will have to add these three groups
along with the number of Hot Shots * $50 plus the number of Our Truck *
$50.

I am so sorry that I keep having to ask for help, but I really need the
answer before I have to send out the invoice on Monday. And I really do
appreciate your help. --Linda

Ken Snell said:
If I'm understanding your question correctly, you could open Sorting &
Grouping, and have a group that is based on this:
DatePart("ww",[NameOfDateField])
Select the "Group Footer" for this group, and put the Sum textbox in it
to
sum the desired values for that week.

--

Ken Snell
<MS ACCESS MVP>



Thank you, Ken. I knew the first part was something very simple.

As for the second part in a query, I could not figure out the syntax to
get
the total for the date period to group them in a week's time in the
query.
I
knew the report could automatically calculate the sums of the
quantities
for
me in a week's time. The field is RefDate and each is a single date
with
each record. (With each date, a certain amount of pallets are worked;
that
is where the other information came from.)

Any suggestion on how to get the date to group in the query? Thanks.
--Linda

:

Your VBA syntax is wrong; try this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 And Me.WeekTot < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 And Me.WeekTot < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

Personally, I'd use a Select Case:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
Select Case Me.WeekTot
Case Is <= 330
Me.WeekPrice = Me.WeekTot * 7.5
Case Is < 620
Me.WeekPrice = Me.WeekTot * 5.25
Case Is < 744
Me.WeekPrice = Me.WeekTot * 5.1
Case Else
Me.WeekPrice = Me.WeekTot * 4.9
End Select
End Sub


Regarding your report footer not summing correctly -- that is because
WeekPrice is not a field in the report's recordsource. Sum works on
the
recordset's fields, not on unbound controls on the report. You'll need
to
use a different method to get the sum -- perhaps you may have to put
the
above calculations into the report's recordsource query as a
calculated
field and then you can bind a control to it for display and then you
can
sum
it in the report's footer section. Alternatively, you could use code
to
read
through the report's entire recordsource's records and recalculate the
individual WeekPrice values and then sum them in the code, then write
that
value to the report's control in the report footer section.

I'd personally try to do the calculation in the query and bind a
control
to
that calculated field.
--

Ken Snell
<MS ACCESS MVP>



(Using Access 2000 on Windows XP.) I have a report tied to a table.
In
the
report of the GroupFooter1 section, I need to add code to either the
OnFormat
or OnPrint event, I think.

Qty is being added based on a weekly total. I then need to use IF
statements to get the right figure to multiply the total within that
week.
I
cannot get it straight on what I need to do, and really need some
expert
help.

Here is the coding for the OnFormat event of the RefDate Footer (the
totals
are grouped on the week):
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As
Integer)
If Me.WeekTot <= 330 Then
Me.WeekPrice = Me.WeekTot * 7.5
ElseIf Me.WeekTot >= 496 < 620 Then
Me.WeekPrice = Me.WeekTot * 5.25
ElseIf Me.WeekTot >= 620 < 744 Then
Me.WeekPrice = Me.WeekTot * 5.1
ElseIf Me.WeekPrice > 743 Then
Me.WeekPrice = Me.WeekTot * 4.9
End If
End Sub

WeekTot is what I have titled the field that will add the Qty field
from
the
table, and those are calculating correctly inside the group footer.

The unbound control titled WeekPrice is what I am getting to come
up;
however, here are the results of the report (note that the numbers
in
parentheses should be the multiplier, but the second result for 663
is
INCORRECT and has actually been multiplied by 5.25 and not 5.1):

5 (7.5) = 37.50
663 (5.1) = 3480.75*
544 (5.25) = 2856

Can someone help me figure out what I have done wrong? I know this
must
be
an error on my part, and I am still learning about VBA. I always
use
DAO
when I can.

Thank you. --Linda
Click to expand...
Click to expand...
Click to expand...
 
Top