# Thread: Zero in calculated field (A2K SP-3)

1. ## Zero in calculated field (A2K SP-3)

I have created a new calculated field in qryRentCalc0 to determine if any invoiced or past due receipt has received a partial payment. The Calculated field is a follows:

PreviouslyPaid: IIf (([PayID]=7 or [PayID]=9 and [RentPaid2]>0, [RentPaid2] +IIf(([PayID2]=7 or [PayID2]=9) and [RentPaid]>0, [RentPaid],0))

For example:
RentPaid = \$945.00
PayID= 7 (Invoiced)
RentPaid2=\$!00
PayID2= 4 (VISA)

The query returns \$100 that is the intended result. The result of the query is also used in a report. RptInvoice prints the Total Invoice, prints the PreviouslyPaid amount and then prints the difference as the net invoice due. This works find when there is a value in PreviouslyPaid. If RentPaid2 has a \$0.00 value, the PreviouslyPaid field is blank. I need that field to have a zero so the field will print and the net invoice calculation will work.

For example:
RentPaid = \$1045.00
PayId=7
RentPaid2=\$0.00
PayID2=null

Currently rptInvoice would print Total Invoice of \$1045.00, PreviouslyPaid is blank and Net Invoiced is blank.

What I need is Total Invoice = \$1045.00, PreviouslyPaid = \$0.00 and Net Invoice \$1045.00

2. ## Re: Zero in calculated field (A2K SP-3)

The cause is not RentPaid2 being 0, but PauID2 being null. The condition [PayID2]=7 doesn't evaluate to False but to Null. Add another condition:

PreviouslyPaid: IIf(IsNull(PayID2]),0,IIf (([PayID]=7 or [PayID]=9 and [RentPaid2]>0, [RentPaid2] +IIf(([PayID2]=7 or [PayID2]=9) and [RentPaid]>0, [RentPaid],0)))

3. ## Re: Zero in calculated field (A2K SP-3)

Many thanks as usual.

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
•