Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IsNull statements (Access XP)

    I have a different problem now with my statement report. When there has been no payments made, in other words there is no value in the subrptPayments, the control that normally would display the payments total displays #Error.

    So I thought the appropriate way to deal with this would be an IIF(IsNull) statement. I finally used this statement
    =(IIf(IsNull(subrptPayments.Report!PaymentAmoung), "$0,00",(subrptPayments.Report!SumofPayments)) )
    and received no error messages when I ran the report. But I am still seeing #Error when there has been no payments in a specific period. I would appreciate any help. I think this is the last problem I haven't been able to figure out on this report.

    Thank you Fay.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: IsNull statements (Access XP)

    You want to do the IsNull check on each individual payment rather than on the sum. Then you can do the sum and if there are no payments, the sum will be zero.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull statements (Access XP)

    Okay that made sense. I used the following =(IIf(IsNull([PaymentAmount]),"$0.00",(Sum([PaymentAmount]))))
    If I work with the report holding the control and run the queries on dates without payments the control, SumofPayment, shows $0.00 like it is suppose to. Once that report is placed on the next report I still get the #Error display. I know that the control on the second report is working correctly if there are payments.

    Any thoughts? Fay

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IsNull statements (Access XP)

    Try this in the control on the main form:
    =IIf(IsNumeric([paymentssubreport].Report!ctrSumofPayments),[paymentssubreport].Report!ctrSumofPayments,0)

    edited by John shortly afterwards
    I have just been dealing with the same question with a form/subform situation rather than a report/subreport.

    If the subform has its "Allow Additions" property set to yes, then the subform has an empty record in it, so I can transfer the total using if isnull or NZ. Once I set the AllowAdditions property to No, this ceases to work because there are any records in the subform. The report situation behaves the same as the form with AllowAdditions set to no.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IsNull statements (Access XP)

    Is this due to the fact there are no records for the subreport named subrptPayments?

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull statements (Access XP)

    John you rock! Bless and thank you. I haven't seen the IsNumeric function before. I will study up on this function.

    To Pat yes this is because there are no records in the table for time period.

    Fay

Posting Permissions

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