Solved

Nz still computes #Error Access

Posted on 2010-08-15
39
559 Views
Last Modified: 2012-05-10
I have a total in a subform form footer that creates a null value if there is no record associated with it and I keep getting the #Error when I try to total the equation below in my parent form. I still need it to change the null to a zero in the equation so it will compute. I used the Nz function but still get the error.

=[Qty]-Nz([InvenPull subform Ord Proc].[Form]![Text4],0)

Any help?
0
Comment
Question by:NuclearOil
  • 14
  • 13
  • 12
39 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
try this:
         =[Qty]-Nz(Forms![YourMainForm]![InvenPull subform Ord Proc].[Form]![Text4],0)
                       Replace [YourMainForm] with the actual name of your main form
0
 

Author Comment

by:NuclearOil
Comment Utility
Still gave me the same error. Is there a way to use the IIF function to show the control value if one part of the equation is null? It would still get me the same result.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
try this, assuming that Qty is a field on your main form bound to a textbox control on the main form named txtQty.  
1.  place a textbox in your subform footer named txtTotal and bound to the expression:
                                = Sum(nz([YourSubformField],0)          ' note sum of field name not control name
2.  place a hidden(visible =no) textbox on main form named txtSubformTotal and bound to the expression:            = Forms![YourMainForm]![InvenPull subform Ord Proc].[Form]![txtTotal]
3. the calc field on your main form s/b bound to this expr:
               = nz([Qty],0)-([txtSubformTotal])   'note txtSubformTotal is the name the hidden textbox control


                       
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
If all else fails, try wrapping it in the IsError function:

IIF(IsError(SomeExpression),"",SomeExpression)
0
 

Author Comment

by:NuclearOil
Comment Utility
puppydogbuddy,

I still can't get that one to work. Is Nz only to be used in formula's in querys?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
no, it can be used in code behind your form. If you are still having problems, break them down into workable chunks as follows:
1.  Were you able to get a total in the subform footer for the textbox named txtTotal?  If not, post your formula.
2.  were you able to get a total for the hidden textbox in the mainform named txtSubformTotal? If not, post your formula.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
First, you cannot get a total of a Textbox (Text4) you can only get a total of a Field.

Second, in this situation the value is NOT null, it just does not exist.
If any part of this formula was Null, then you would get a "Invalid Use of Null" error, Not #ERROR.

Third, if these values are on the subform itself, then the syntax can probably be shortened to:
=[Qty]-[YourMysteryField]

Finally, if your goal is to display 0 if there are no subform records, then you can try something like this:

=IIf(DCount("SomeField","YourChildTable","SomeID=" & [SomeID])=0,0,[Qty]-[YourMysteryField])

Here is a sample.
Notice that if you select Customer: FISSA...
...0 displays in the Subform footer (this would normally be blank)

Study it carefully, I am sure you can adapt it to work in your database.

;-)

JeffCoachman
Access-EEQ-26404183-SubFormERROR.mdb
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Hi Jeff,
<<<First, you cannot get a total of a Textbox (Text4) you can only get a total of a Field.>>>
If you are refering to my statement, you know from my second post that I am refering to the total of the textbox as a result of the textbox being bound to an expression like the one below from my second post:

           1.  place a textbox in your subform footer named txtTotal and bound to the expression:
                                = Sum(nz([YourSubformField],0)          ' note sum of field name not control name

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
puppydogbuddy,

I based my post on the original question.
As you saw, I simply listed the issues with doing something like this.

I was in no way saying anything against you or your post.
Your reputation here for providing exemplary solutions speaks for itself.
And I have learned quite a bit from your posts.
Besides, I try to stay on the good side of Experts who I might need help from in the future...
;-)

I did see your post, but since I was a little unclear of the why asker had an expression that displayed a "Total":
<I have a total in a subform>
...but yet it did not contain a "summary" (ex.: Sum(), Dsum(),,...etc).
I was not sure if the asker had the correct formula for the textbox to get the result they were expecting..

So I just figured that I would just list all the potential problems that the asker may run into.


The subject of #Error in subforms/subreports with no records is a common question in the Reports Zone, so my post was my standard reply.
(JDettman. also has a neat way of dealing with this)
;-)

Also, seeing that they seemed to want to display zero when no subform records were present, is what lead to the Dsum() in my sample.

Finally, in some cases (from my experience, and by it actually occurring while I made the sample), you will get the #Error when the DB is not compacted for some reason.
For example I got the sum of the freight Field and multiplied it by a value (trying to simulate the askers situation):
=Sum([SomeField])*[SmeOtherValue]
this worked fine...
Then I created another textbox with this:
=Sum([SomeField])
...and I got #Error for both!
I deleted the second control, and compacted the DB.
I then recreated the control and it worked fine...

So compacting the DB (and perhaps compiling any code) may also clear up the #error.

;-)

Jeff
0
 

Author Comment

by:NuclearOil
Comment Utility
Boag 2000 & PuppyDogBuddy,

I attached my db. I tried your IIF DCount statement. If you look at the Order Processing form, the subform that has the field Total qty removed is what I need to total even if records don't so I can have a field that totals that field minus the qty field for that stock lot.

Please let me know what I'm doing wrong.
-Error.mdb
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
NuclearOil,
Took a brief look-one problem is that QtyRemoved is on a nested subform within a subform, so right off the bat, your referencing is incorrect.  I am busy at the moment, but Jeff or I will get back to you as son as we can.

Jeff,
We have collaborated on many solutions together.  I have learned a lot from you also, and never thought for a moment that you were criticizing my comments....I thought maybe you did not see my earlier post. Talk to you later.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Ahhh.

This is a subform OF a subform (a sub-Sub Form)

First here is the correct formula for the Textbox:
=IIf(DCount("[Removal ID]","[Order Proc InvNPO Query]","[Qty Removed]=" & [Qty Removed])=0,0,Sum([Qty Removed]))

Note that if you use names with spaces in them (Not recommended, see rule number 3 here: http://www.mvps.org/access/tencommandments.htm), you will need to encase the Names in square brackets.
Also in my sample I was working with a text string, in your case it is a number, so  the double/single quotes were not needed. (that was an oversight on my part...)
I also keyed the formula off of the ID Field, because this will be unique for each record.
Finally, the "Aggregate Functions" (Dsum(), Davg(), ...etc), like to use "table/query" names.
I think you were trying to reference the form field names.
;-)

As for displaying Zero when the "First subform" (InventoryNPO subform1), has no records, I would not worry about this, IMHO. I don't think it is worth the trouble.
It will be blank, signifying no records.
You would have to wrap the formula in yet another DCount()!
(Now there are, of course, other ways to do this, but I use Dcount for clarity)

Again, if this is a hard requirement, then I am sure that puppydogbuddy can help you with that portion.

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<Jeff,
We have collaborated on many solutions together.  I have learned a lot from you also, and never thought for a moment that you were criticizing my comments....>

So we are are still BFF's?
OMG! That's is so like, ...totally kwel!

Jeff...
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Jeff,
LOL!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0
 

Author Comment

by:NuclearOil
Comment Utility
Thanks for everything gents,

 I know I got some naming issues that I have corrected. I pretty much self taught on Access.

How would I reference the IIF Dcount to do my subtraction from my qty field?

=[Qty]-[InvenPull subform Ord Proc].[Form]![Text4]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I don't see a control with that controlsource in the sample you posted...?
0
 

Author Comment

by:NuclearOil
Comment Utility
Boag200,

Sorry

I forgot to put the text in on the last post. I'm trying to get control Text27 to just return the qty even if a record doesn't exsist (look at record 2) using the IIE function.
-Error.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<How would I reference the IIF Dcount to do my subtraction from my qty field?
=[Qty]-[InvenPull subform Ord Proc].[Form]![Text4]>
For what condition?

The basic Syntax for Dcount is:
Dcount("Some Field", "Some TableOrQuery", "SomeCriteria")
("Some criteria" is optional.)

So your logic would look like this
IIF(Dcount("Some Field", "Some TableOrQuery", "SomeCriteria")=0,"",[Qty]-[InvenPull subform Ord Proc].[Form]![Text4])

(You can use the formula I gave you as a template)

<I pretty much self taught on Access. >
Remember, that the Access Help files are a wealth of information

I am sure this will be a fun project for you.

;-)

JeffCoachman
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:NuclearOil
Comment Utility
I think I have the templete correct but I still get an error. How do I get the IIF function to evaluate the expression if a record doesn't exsits? Is this expression also changing Text 4 to zero to fininh the equation?


=IIf(DCount("[Text4]","[InvenPull subform Ord Proc]","[Text4]")=0,"",[Qty]-[InvenPull subform Ord Proc].[Form]![Text4])
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again, if "What* form has no records?

In any event...

Again, dcount will count values in a "Table or Query" (See my original formula)
The first Argument is the "Field" (again see the help files on Dcount())
You are trying to do this with a "Control" ([Text4])

Basically you need to find the "Table Field" that you are trying to count.

The Third argument is the "Criteria"
Typically this take the form: "Something=SomeThingElse"
In other words, it is rarely 1 value, as you have.

So again a sample of Dlookup would be:
Dcount("[CustomerID]",'[tblCustomers]","CustomerCountry=" & "'" & me.cboSelectedCountry & "'")
This will count all the CustomerID's of all the customers in tblCustomers, *WHERE* the Customer's country matches what is in a Country combobox.
(If the Combobox value was a string)

If the combobox value referenced a Number, it might look like this:
Dcount("[CustomerID]",'[tblCustomers]","CustomerCountryID=" & me.cboSelectedCountry)

So again, reference the help files and study my sample,

I am sure you can be successful.
(The difficulty in doing this is why I suggested that you simply leave it empty (normal) if no records are present...)

;-)

Jeff
0
 

Author Comment

by:NuclearOil
Comment Utility
I've been trying to get the HasData property to force the Text4 value or assign it zero, but Access want to keep bracketing the HasData and then it errors becasue it thinks its a field. What is wrong with this expression,

=IIf([InventoryNPO subform1].[Form]![InvenPull subform Ord Proc].[Form].HasData=True,[InventoryNPO subform1].[Form]![InvenPull subform Ord Proc].[Text4],0)
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
I think you left out the reference to the Main form in the first part of the expression.

Try changing this part:
         =IIf([InventoryNPO subform1].[Form]

To this:
        =IIf(Forms![YourMainForm]![InventoryNPO subform1].[Form]
           use your actual main form name in place of [YourMainForm]
0
 

Author Comment

by:NuclearOil
Comment Utility
Nope,

Still want to brack the HasData expression like a field.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Then try removing the reference to [Form] in the part of the expression before the HasData:
change this:    [InvenPull subform Ord Proc].[Form].HasData
               
                to:   [InvenPull subform Ord Proc].HasData
0
 

Author Comment

by:NuclearOil
Comment Utility
Negative.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
just got back online:

=IIf([InventoryNPO subform1].[Form]![InvenPull subform Ord Proc]![Text4].HasData=True,[InventoryNPO subform1].[Form]![InvenPull subform Ord Proc].[Text4],0)
0
 

Author Comment

by:NuclearOil
Comment Utility
I get the #Name? now?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Per this link:    http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/43395/Calculated-control-Name-error

1. Check the Name property of the problem text box. It must not be the same as
the name of a field in the form's RecordSource.
2. Make sure Name Autocorrect is not turned on.

0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<I've been trying to get the HasData property to force the Text4 value or assign it zero, >
My working IIF did not contain a .HasData
You have never stated what form being blank will trigger the Zero?
0
 

Author Comment

by:NuclearOil
Comment Utility
The subform(sub-subform) InvenPull subform Ord Proc when is blank should trigger the zero.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Jeff is absolutely correct about not using the HasData property.  According to these links and others, the hasData property is used only for reports, not forms.
     http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/29011/HasData-property-for-a-subform
     http://msdn.microsoft.com/en-us/library/aa195920(office.11).aspx
     http://office.microsoft.com/en-us/access-help/i-see-error-displayed-in-a-control-HA001181448.aspx

Further, the first link above recommends the following expression (in code behind the subform) to test if a form's recordset is empty:
numRecs = Me!sfrmcontrol.Form.RecordsetClone.RecordCount
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 125 total points
Comment Utility
Here is the formula:

=IIf(DCount("[Stock Number]","[Order Proc InvNPO Query]","[Removal ID]=" & [Record Id])=0,0,([Qty]-[InvenPull subform Ord Proc].Form!Text4))

I will disclaim that I do not fully understand your table/query design and relationships. I only state that this formula will return zero when no records are present for the subfrom.
;-)

I will also state that the reason that this is so awkward is because the result should be Null,(Blank) ...not zero, as you are requesting.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Oh,

And out of respect for puppydogbuddy, I will also state that this may not be the only way that this can be done. (...or be the most elegant method)

I only, state the this is what I would be inclined to use.

If puppydogbuddy can get a you the same result, using a different line of reasoning, then feel free to split the points.

It is always best to have more than one way of doing something.

;-)

JeffCoachman
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 125 total points
Comment Utility
my solution is based on computing the lowest detail level total first (wrapped in the nz function) and then rolling it up to the next level until you reach the summary level as outlined in this link and my first post.  The key is to get the references correct between the form and subforms:

                      http://office.microsoft.com/en-us/access-help/about-calculating-a-total-in-a-subform-and-displaying-it-on-a-form-HP005187909.aspx

In this case, there are 3 levels: the lowest detail level is the nested subform, so you would place a textbox in the footer of the nested subform, then reference this nested subform textbox from a textbox in the subform or main form as applicable.

 
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Here is another good reference for rolling/carrying totals between subforms and forms.
                 http://www.databasedev.co.uk/subform-total.html
0
 

Author Closing Comment

by:NuclearOil
Comment Utility
Thanks for your guys help? Sorry for delay. It did work out.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Great!

;-)

Jeff
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now