Results 1 to 6 of 6
  1. #1
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    #Error in Control with a subform control source

    Hello,

    I have a form which is basically a Student ledger. A subform shows payments made. At the bottom of the form, I have some controls that summarize the student's account, including a control called PaymentsMade. The control source is = [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount].

    It works perfectly when a student has at least one payment, but I keep getting #Error whenever a student has not made any payments. Entering a $0 payment for the student solves the problem, but obviously, that is a poor option.

    I have tried:
    Nz([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount],0) as well as
    IIF([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount] Is Null, "0", [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount]) but neither solved the problem.

    Any suggestions would be greatly appreciated.

    Thanks in advance,
    Al

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One way:

    nnz function
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67

    Nz Function didn't solve the problem

    Thank you for responding.

    I have tried:
    Nz([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount],0) as well as
    IIF([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount] Is Null, "0", [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount]) but neither solved the problem.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, those were in your original post, and I wouldn't expect either to work. That's why I posted something else.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    alsoto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    67
    Pbaldy,

    I'm sorry, I thought NNZ was a typo for NZ. I followed the link and found:

    Function nnz(testvalue As Variant) As Variant
    'Not Numeric return zero
    If Not (IsNumeric(testvalue)) Then
    nnz = 0
    Else
    nnz = testvalue
    End If
    End Function

    How do I implement that? I have never used a global module.

    Thanks,
    Al

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Just copy paste that code into a standard module (don't name it "nnz" if you create a new module). If you don't already have one, Insert/Module from the VBA editor.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-03-2011, 08:58 AM
  2. Charts within subform in a control source
    By GraemeG in forum Forms
    Replies: 0
    Last Post: 06-03-2011, 04:38 AM
  3. Control Source
    By sarah54 in forum Access
    Replies: 1
    Last Post: 03-07-2011, 09:00 PM
  4. Replies: 4
    Last Post: 10-07-2010, 09:42 AM
  5. Control Source for Text Box (#error)
    By km8415 in forum Forms
    Replies: 3
    Last Post: 06-27-2010, 10:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums