-
#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
-
-
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
-
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?
-
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
-
Are the Payment and Expense forms SubForms of your current form or separate forms in your system?
-
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.....
-
I'm afraid I simply do not understand your table and form structure.
-
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
-
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.
-
I am reading your forum jjwrightaz and I cant seem to understand what you mean. can you clarify your english.
-
Thanks again for your help. It seems to be working now with an Nz function.
-
Excellent! Thanks for posting back with your success.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules