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

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •