Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Breaks (A2K SP-3)

    I currently have a report, rptIncomePosting, that breaks based on the following:
    =IIf(IsNull([DepositDate]),

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

    Re: Report Breaks (A2K SP-3)

    Try this: first, test if DepositDate is Null. If so, test if PayID =10 or PayID2 = 10.

    =IIf(IsNull([DepositDate]), IIf([PayID]=10 Or [PayID2]=10, "Paid By Deposit: " & [RentPaidDate], "Credit Cards: " & [RentPaidDate]), "Deposit Date: " & [DepositDate])

    If you want to be a tiny bit more efficient, shorten it to this:

    =IIf(IsNull([DepositDate]), IIf([PayID]=10 Or [PayID2]=10, "Paid By Deposit: ", "Credit Cards: ") & [RentPaidDate], "Deposit Date: " & [DepositDate])

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    Thanks for the assistance. I used your last suggestion and this works fine except when I try to total by group.

    I have a Day Total which is working fine. When I try to total by group, the Paid by Deposit is included with Credit Cards. The Deposit Date group total correctly . The Grand Total is also working correctly.

    Any suggestions?

    Tom

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

    Re: Report Breaks (A2K SP-3)

    Please post a stripped down copy of the database.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    Enclosed is a stripped down database.

    Tom
    Attached Files Attached Files

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

    Re: Report Breaks (A2K SP-3)

    This version does not use the expression you asked for in this thread <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    When I unzip the attachment, 4-394942Woody1.zip and open Wood1.mdb and rptIncomePositing in design mode, the header contains the formula you suggested: i.e.

    =IIf(IsNull([DepositDate]),IIf([PayID]=10 or [PayID2]=10,"Paid By Deposit: ", "CreditCards: ") & [RentPaidDate], "DepositDate: " & [DepositDate])

    Page 1 of the report contains credit dates from 07/26/2004 through 07/28/04. The day total for credit card 07/28/08 is at the top of page 2.

    Next is the Paid By Deposit for 07/30/2004 which has 1 record with the Day Total followed by the Group Total. The Group Total is for the Credit Cards and Paid by Deposit. There should be a Group total for the Credit Cardsa and another Group Total for Paid by Deposit.

    The group Total for the Deposit Date is correct.

    Tom

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

    Re: Report Breaks (A2K SP-3)

    This is what I see:
    Attached Images Attached Images
    • File Type: png x.png (2.9 KB, 0 views)

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    I do not understand what need to be in the Sorting and Grouping box.

    Tom

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

    Re: Report Breaks (A2K SP-3)

    I'm sorry, I haven't been clear enough. You do have a text box in the group header with control source

    =IIf(IsNull([DepositDate]),IIf([PayID]=10 Or [PayID2]=10,"Paid By Deposit: ","Credit Cards: ") & [RentPaidDate],"Deposit Date: " & [DepositDate])

    but that does not mean that the report uses this expression to group the report on. The group footer containing 'Group Total' is on

    =IsNull([DepositDate])

    and this expression does not distinguish between 'Paid By Deposit' and 'Credit Cards', so they get lumped together in the sum. The grouping expression should be something like this:

    =IIf(IsNull([DepositDate]),IIf([PayID]=10 Or [PayID2]=10,3,2),1)

    In fact, I think that the grouping levels and their group headers and text boxes should be set up differently. See what you think of the attached (apart from aesthetics).
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    This still does not get what I am trying to accomplish.

    For each Day for each Group (Credit Cards, Paid From Deposit and Deposits), I want a Day Total
    For each of the 3 groups, I want a Group Total after all the days have been printed for that group
    and finally, I want a grand total after all 3 group have been printed

    For expampe, for 07/26/04, 07/27/04 and 07/28/04 your example gives a Group Total followed by a Day total

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

    Re: Report Breaks (A2K SP-3)

    OK, just exchange the two grouping levels
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Breaks (A2K SP-3)

    Thanks for all your assistance.

    Tom

Posting Permissions

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