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

    Report Calculations (A2K SP3)

    Edited by HansV to display values in table

    I have a formula in the detail section of report as follows:

    =IIf([PayID]=2 or [PayID]=8, [RentPaid], IIf([PayID2]=2 or [PayID2]=8, [RentPaid2], IIf(([PayID]=2 or [PayID]=8) and [PayID2]=2 or [PayID2]=8, [RentPaid] + [RentPaid2],0)))


    In the Deposit Date footer of the report, I have the following:
    =Sum(IIf([PayID]=2 or [PayID]=8, [RentPaid], IIf([PayID2]=2 or [PayID2]=8, [RentPaid2], IIf(([PayID]=2 or [PayID]=8) and [PayID2]=2 or [PayID2]=8, [RentPaid] + [RentPaid2],0))))

    I have 2 fields in tblRental to record payments
    RentPaid
    RentPaid 2

    For those 2 fields there is an associated PaymentMethod
    PayID
    PayID2

    For the PayIDs 2=Checks 8=TravelersChecks 7=Invoiced

    What I am trying to do is get a total of Cash and Travelers Checks. The report totals by deposit dates

    For Friday, January 23, 2004 the following records are displayed

    <table border=1><td>Receipt #</td><td>Total</td><td>Checks</td><td>Rent Paid</td><td>PayID</td><td>Rent Paid</td><td>PayID2</td><tr><td align=right>35389</td><td align=right>$599.00</td><td align=right>$599.00</td><td align=right>$599.00</td><td align=right>2</td><td align=right>

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

    Re: Report Calculations (A2K SP3)

    Since we don't know the data from which this is derived, it is hard to determine what is going on.

  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 Calculations (A2K SP3)

    Attached is a scaled down version of the database.
    Attached Files Attached Files

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

    Re: Report Calculations (A2K SP3)

    The main problem with your report is that the text box bound to the TotalRentPaid field has been named RentPaid. Therefore, the expressions using RentPaid don't refer to the RentPaid field but to the RentPaid control and hence to the value of TotalRentPaid. Solution: change the name of the text box bound to TotalRentPaid, for example to txtTotalRentPaid.

    Other remarks: I think the expressions in the detail section should be

    =IIf([PayID]=1,[RentPaid],0)+IIf([PayID2]=1,[RentPaid2],0)
    =IIf([PayID]=2 Or [PayID]=8,[RentPaid],0)+IIf([PayID2]=2 Or [PayID2]=8,[RentPaid2],0)

    and in the report footer section:

    =Sum(IIf([PayID]=1,[RentPaid],0)+IIf([PayID2]=1,[RentPaid2],0))
    =Sum(IIf([PayID]=2 Or [PayID]=8,[RentPaid],0)+IIf([PayID2]=2 Or [PayID2]=8,[RentPaid2],0))

    And in qryDeposit, the condition Between [ReportBeg] And [ReportEnd] should be in both criteria lines.

  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 Calculations (A2K SP3)

    Hans,
    Many thanks for your sage advise. You solutions were, as usual, just what I needed.

    I learned what problems can result from using the same name for fields and controls. I will review previous work to eliminate this potential problem.

    Many thanks.

    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
  •