# Thread: Report Calculations (A2K SP3)

1. ## 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. ## 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. ## Re: Report Calculations (A2K SP3)

Attached is a scaled down version of the database.

4. ## 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. ## 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
•