Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group Suming (2003)

    In the group footer on my report I have a text box to sum gross purchases if the invoice year date field is 04 and another text box to sum gross purchases if the year date field is 03. The 04 text box sums fine the 03 box displays nothing. If I run my query just on 03 records then the 03 box will display. Any ideas on how I can display both 03 and 04 gross purchase totals? The following is what I have for the control source.

    TextBox for 04. =IIf([Invoice Date Year]=Forms![ERP Reports]!txtEndYear,Sum([Gross Purchases]-[Freight Parts and Warranties]))

    TextBox for 03. =IIf([Invoice Date Year]=[Forms]![ERP Reports]![txtBeginYear],Sum([Gross Purchases]-[Freight Parts and Warranties]))

    Thanks,

    Kent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Group Suming (2003)

    Your expressions either sum everything or nothing, depending on the value of the year in the last record, they don't sum specific records. Try this:

    =DSum("[Gross Purchases]-[Freight Parts and Warranties]","NameOfTable",[Invoice Date Year]=Forms![ERP Reports]!txtEndYear")

    =DSum("[Gross Purchases]-[Freight Parts and Warranties]","NameOfTable",[Invoice Date Year]=Forms![ERP Reports]!txtBeginYear")

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group Suming (2003)

    Han's, I tried your expressions and now the report sits there a long time saying formatting page and I have to hit control break to get out. Any ideas. I used my query name rather than a table name.

    Thanks.

    Kent

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Group Suming (2003)

    Does this work better?

    =Sum(IIf([Invoice Date Year]=Forms![ERP Reports]!txtEndYear,[Gross Purchases]-[Freight Parts and Warranties],0))

    and similar for the other.

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group Suming (2003)

    Excellent! That did the trick. Thanks Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •