SubTotaling on a Form with multiple Sub Forms

[mechanicalscarecrow]

Hello everyone, I am new to access and am trying to create a database to help create invoices. The form and subforms are based on queries which are based on tables. The two subforms are to separate taxable products with non-taxable products. I have followed this link: http://www.ms-access2010.com/tutorials/forms2.html to create it, only using the two subforms instead of one.



The issue is, not every invoice has both non-taxable and taxable products. Most have one or the other, however the equations only work when I have both. I have tried multiple different options, but have yet to find the correct way to do it.

Here's what I have tried.

=[TaxDetailSubF].[Form]![SubTot]+[NonTaxableDetailF].[Form]![SubTot2] This code has given me a #Type! error in the invoices that do not have both.

=Nz([TaxDetailSubF].[Form]![SubTot],0)+Nz([NonTaxableDetailF].[Form]![SubTot2],0) This has given me a #Size! error

=Nnz([TaxDetailSubF].[Form]![SubTot],0)+Nnz([NonTaxableDetailF].[Form]![SubTot2],0) This has given me a #Name? error.

If you need anymore information on my database please let me know. I've been trying to figure out this problem for a few days now. Odds are, it's probably an easy fix, I'm just missing it.

Thank you for your help!

[June7]

Did you place the Nnz() custom function in a general module?

I tested the Nnz() function and cannot replicate the issue. Must be something about your form design. If you want to provide db for analysis, follow instructions at bottom of my post.

[mechanicalscarecrow]

I didn't put it in a module I don't think. Do I need to? I've been placing all of my equations in the Control Source on the Property Sheet for the field that I want it to appear in. How would I go about putting it in a module.

Thank you!

[June7]

Where did you learn about Nnz()? It is a custom user defined function - VBA code - whereas Nz() is an intrinsic function.

http://access.mvps.org/access/forms/frm0022.htm

[mechanicalscarecrow]

It was actually on this forum that I learned about Nnz() I believe. I was researching my problem and someone had pretty much the exact same situation. However, I could not follow exactly how it was solved.

[June7]

Put the Nnz() function code into a general module so it can be called from anywhere, including queries.

[mechanicalscarecrow]

Ok! I have one more questions, and I apologize because it is probably a very basic concept. How do I call it to the form I want to use it in?

Thank you so much for all the help you have been doing!

[mechanicalscarecrow]

'This code was originally written by Keri Hardwick. 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged.''Code Courtesy of'Keri Hardwick'
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

Also, above is the code that I was referring to earlier. Is it fine as is and I can copy and paste it into the module?
Thanks again!

[June7]

Code looks fine to me. Paste it into a general module.

Call it from query or textbox ControlSource (as shown in your OP).

[mechanicalscarecrow]

I am confused as to how to call it in. I've called the module TestModule and in the expression builder for the Control Source of the text box I have tried to put in =[TestModule], to which I get a #Name? error.

[June7]

Call the function, not the module. Like the attempt you posted in first post.

[mechanicalscarecrow]

When I do that, it pops up saying: The expression entered has a function containing the wrong number of arguments.

=Nnz([TaxDetailSubF].[Form]![SubTot],0)+Nnz([NonTaxableDetailF].[Form]![SubTot2],0)

I'm sorry for all the trouble this is becoming. Thank you for all of your help!

[mechanicalscarecrow]

Oh wait! I took out the ,0 from each one and It works! Thank you!!!