[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Nz still computes #Error Access

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
NuclearOil
Asked:
NuclearOil
  • 14
  • 13
  • 12
2 Solutions
 
puppydogbuddyCommented:
try this:
         =[Qty]-Nz(Forms![YourMainForm]![InvenPull subform Ord Proc].[Form]![Text4],0)
                       Replace [YourMainForm] with the actual name of your main form
0
 
NuclearOilAuthor Commented:
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
 
puppydogbuddyCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
If all else fails, try wrapping it in the IsError function:

IIF(IsError(SomeExpression),"",SomeExpression)
0
 
NuclearOilAuthor Commented:
puppydogbuddy,

I still can't get that one to work. Is Nz only to be used in formula's in querys?
0
 
puppydogbuddyCommented:
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
 
Jeffrey CoachmanCommented:
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
 
puppydogbuddyCommented:
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
 
Jeffrey CoachmanCommented:
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
 
NuclearOilAuthor Commented:
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
 
puppydogbuddyCommented:
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
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
<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
 
puppydogbuddyCommented:
Jeff,
LOL!!!
0
 
Jeffrey CoachmanCommented:
;-)
0
 
NuclearOilAuthor Commented:
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
 
Jeffrey CoachmanCommented:
I don't see a control with that controlsource in the sample you posted...?
0
 
NuclearOilAuthor Commented:
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
 
Jeffrey CoachmanCommented:
<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
 
NuclearOilAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
NuclearOilAuthor Commented:
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
 
puppydogbuddyCommented:
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
 
NuclearOilAuthor Commented:
Nope,

Still want to brack the HasData expression like a field.
0
 
puppydogbuddyCommented:
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
 
NuclearOilAuthor Commented:
Negative.
0
 
puppydogbuddyCommented:
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
 
NuclearOilAuthor Commented:
I get the #Name? now?
0
 
puppydogbuddyCommented:
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
 
puppydogbuddyCommented:
0
 
Jeffrey CoachmanCommented:
<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
 
NuclearOilAuthor Commented:
The subform(sub-subform) InvenPull subform Ord Proc when is blank should trigger the zero.
0
 
puppydogbuddyCommented:
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
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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
 
puppydogbuddyCommented:
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
 
puppydogbuddyCommented:
Here is another good reference for rolling/carrying totals between subforms and forms.
                 http://www.databasedev.co.uk/subform-total.html
0
 
NuclearOilAuthor Commented:
Thanks for your guys help? Sorry for delay. It did work out.
0
 
Jeffrey CoachmanCommented:
Great!

;-)

Jeff
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now