Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    #Error

    I've searched several web posts, microsoft, etc and am not having any success solving my #Error message......

    Here's the issue:

    I've created a form which pulls references from a couple of different tables and subforms. I'm trying to populate a field within my master form where subformX - subformY = balance. This works fine as long as there is data within either subformX and subformY. Unfortunately, there is data within subformX and nothing within subformY currently. How do I state within a formula to say iif subformX or subformY is null or IsError, then set the value to 0 and proceed with the formula?

    Any help is greatly appreciated!

    Thanks in advance,


    jw

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Use the Nz() function.

  3. #3
    Join Date
    Mar 2009
    Posts
    6

    Still stuck....

    I attempted my fate with the Nz function. Below is a sample of what I've created:

    =nz(Sum(IIf(IsNull(Forms!Payment!Payment),-Forms!Expense!Expense,IIf(IsNull(Forms!Expense!Exp ense),Forms!Payment!Payment,Forms!Payment!Payment-Forms!Expense!Expense))))

    Unfortunately, I still recieve the #Error message. Other suggestions?

    Thanks!
    jw

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you summing each SubForm in the SubForm? If so, then what is the name of the Summing control in each SubForm? Have you looked at this link before?

  5. #5
    Join Date
    Mar 2009
    Posts
    6
    I hadn't seen that document before. Very useful tool.

    I'm not getting very far with the use of subforms, so within my main form, I have a query which totals 'expenses', another which totals 'payments' and one which does 'payments' (from payments query) minus 'expenses' (from expenses query). What I am experiencing is that if a payment and expense exist, then my math works just fine. If either are missing, I receive the #ERROR. I tried placing default values of zero, the use of the Nz function, IIF statements but nothing seems to be working.

    Again I truely appreciate your assistance.
    jw

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are the Payment and Expense forms SubForms of your current form or separate forms in your system?

  7. #7
    Join Date
    Mar 2009
    Posts
    6
    They are individual queries turned subforms within the main query.

    I don't know if I am making a bigger mess for myself or not, but this formula:

    =Nz(IIf(IsNull(Expense![Grant Number]),0,DSum("[Expense]","Expense")))

    is providing me with the 'Grand Total'. I can't seem to separate the display based on unique ID (Grant Number). For example, if grant 1 has 100 Payment and 0 Expense, but grant 2 has 0 Payment and 100 Expense, when I pull either record within the form, they both show 100 Payment and 100 Expense.....

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm afraid I simply do not understand your table and form structure.

  9. #9
    Join Date
    Mar 2009
    Posts
    6
    Thanks, Allan. Can we try one last item?

    I've removed the subforms and have now a master form. Within the form, I have the ability to enter payments and expenses by 'Grant Number' (this is my primary key).

    I'm attempting to create a formula which will go out to the table where the data is entered into from the form and pull a sum for payments within one text box and a sum for expenses within an additional text box.

    The following is close, but not quite there:

    =IIf(IsNull([jwtest]![Grant Number]),0,DSum([jwtest]![Amount],"Expense"))

    My attempt is this. If within jwtest, a grant number is not there, then enter zero, otherwise, sum the amount where the category is equal to Expense.

    Thanks!
    jw

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can not just specify any table and expect to retrieve some field from it. You either need a RecordSet or a query. The Domain functions can operate on any table you specify but you need to follow the syntax of the command. Here's a link to a good syntax reference for those functions. All of the Domain finctions have the same basic syntax.

  11. #11
    marianne is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Asia Pacific Region
    Posts
    150
    I am reading your forum jjwrightaz and I cant seem to understand what you mean. can you clarify your english.

  12. #12
    Join Date
    Mar 2009
    Posts
    6
    Thanks again for your help. It seems to be working now with an Nz function.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Excellent! Thanks for posting back with your success.

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

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