Re: Programming for footer section

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 [MVP]" wrote:

> 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" <LindaBrown@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:911AD3BF-39FD-41C9-86AA-F66E6C592B11@xxxxxxxxxxxxxxxx
> > 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 [MVP]" wrote:
> >
> >> 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" <LindaBrown@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:5E1CCDA9-BB14-460F-ABA5-BF37804D2277@xxxxxxxxxxxxxxxx
> >> > 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
> >> >
> >> > "Ken Snell [MVP]" wrote:
> >> >
> >> >> 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>
> >> >>
> >> >>
> >> >>
> >> >> "Linda Brown" <Linda Brown@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:C1DEF57F-4663-42C8-8167-352F51B1955E@xxxxxxxxxxxxxxxx
> >> >> > (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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... the sql view and name of your totals query ... number from the $'s being listed more than once in the query. ... Assuming you have groups in your report like ... And you want to display the sum of in the Vendor header or footer ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... My report is grouped by Vendor, then Project, ... source query before putting it in the report so every record shows). ... A Running Sum text box accumlates its total. ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... my query is what's making extra records (and I don't ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • Re: Report is adding too many numbers
    ... my query is what's making extra records (and I don't ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.queries)