Subform Sum error

[williamson1979]

Hello,

I have a error for Sum's in frmAggrSub. I have 4 unbound text boxes in the footer, 1 is a percent of qryProduction so I can understand if its 0 than a error. However the other 3 only Sum entered numbers in the subform. Any help is appreciated

Edit: The uploaded file has a default value of 0 in AggrPerc textbox in frmAggrSub. I just corrected that, it was causing data entry issues. But the rest I stated is still happening

[NeoPa]

You need to put the full question in the thread. Expecting people to upload your database in order to determine what the question should be is not acceptable.

I do explain that elsewhere but it helps that all instances are flagged this way so anyone looking and seeing what goes on here is under no illusion how things are supposed to be done.

I explained before about technical questions but I'll repeat they need to be expressed fully, carefully and using the best terminology you can manage. If you talk about controls but don't explain they're on a Form, or even a SubForm, then our ability to help is severely curtailed. Reading through your question and fixing any problems is expected in all cases but particularly for OPs (OP = Opening Post as well as Opening Poster. It's used here as the former).

Ideally, and very few manage this level of competence, but ideally, questions should be posted and an answer given and that's the end of it. Imagine the value to someone Googling for their problem to find such threads. Simples. Question asked and answered. Perfect. In real life we just aim for that target. It does require a little effort, but not only is that reasonable when asking for assistance, it also has a way of benefitting you too in less obvious ways.

[williamson1979]

I don't know how to explain other than I have a unbound textbox using the sum feature that returns a error unless what seems to be a totally unrelated field in another has a value greater than 0?

[williamson1979]

Im sorry Im aggravating you but I made the changes off other advice offered so now Ive encountered issues that has risen from that which are in my last 2 post. Ill figure out thanks

Mod if possible please delete my account. Thanks Twinny I eally appreciated your help.

[williamson1979]

You need to put the full question in the thread. Expecting people to upload your database in order to determine what the question should be is not acceptable.

I simply post what I see is not working. I uploaded the file in case anyone thought it would be helpful since I do not know the technical terms. I never ask anyone to open it. I know the help offered is free and on your time but understand I don't know enough to be specific.. Just delete the threads and Ill repost with different wording.

[NeoPa]

Aggravating is probably not the right word. Frustrating is nearer the mark.

I'm not simply looking to put you down. I know what a mess we can all get into when things are explained in an imprecise way and questions are asked simply as they occur to the poster rather than them spending time and effort to make them make sense to people who aren't in the room with them. IE. If the whole situation isn't explained but we're left to guess at much of what constitutes the problem.

I say these things because it's what works. Frankly we've seen the meandering threads that you've started so far and they get very little done in a lot of time. It's very little help to you and does very little for us or the website. If you really feel that explaining your problem in a clear and concise way is beyond you then perhaps here isn't where you need to be.

I can honestly say that I feel only that it's a shame we can't make some better contact. I don't feel you've done anything to upset us here. There just seems too big of a gap between how this works (and for practical reasons has to work) and how you think and work. We wish you only the best of luck.

[williamson1979]

I tried a new post. One problem only mentioned. I hope that will be clear and understandable. I’ll worry about the other stuff once that question gets somewhere. Actually a few post got nice results. It’s the little things to tie it all together is harder for me to explain.

[williamson1979]

Inserting this into the query for the subform eliminated the #Num and #Error errors from the subform. I wish this thread would have went somewhere though because it still makes no sense why three of the four fields that were not using ProdSold or ProdWaste for calculations were effected by this.

AggrPerc: IIf([ProdSold]=0 And [ProdWaste]=0,0,[AggrUsed]/([ProdSold]+[ProdWaste]))

[NeoPa]

I don't see anywhere what the whole query looks like in SQL so I can't explain everything for you. However, I did notice one problem with the formula you posted. It seems it should probably be :

IIf([ProdSold]=0+[ProdWaste]=0,0,[AggrUsed]/([ProdSold]+[ProdWaste]))

If you're still experiencing problems with your query try posting in its SQL here and we can try to have a look (Also check out How to Debug SQL String).

[williamson1979]

Thanks. I’ll update with your code. What I put seemed to correct the errors though it might not be the best solution . My biggest confusion was 3 fields of the 4 aren’t linked nor do they divide. They only sum whatever numbers are entered. The other I understand since it uses a field from the main form which has the possibility to be 0. So I can’t wrap my mind around why but clearly there is a why I’m just missing it.

Anyway as I say I’ll enter your code and go from there. If I notice anymore glitchy behavior I’ll post sql.

[williamson1979]

I’ll read the link you posted as well.

[NeoPa]

It doesn't surprise me that many (most) of the situations that had problems disappeared with your earlier code. There are just some possible scenarios where your code would continue to error, even if you haven't seen them yet.

As a matter of interest can you tell me where and/or why? IE. Have you understood the change and why I suggested it?

PS. Yes. Read the link. Most of the real value is in the articles. These are explanations for the most commonly experienced problems, which is why it was worth the extra effort to produce them.

[williamson1979]

As a matter of interest can you tell me where and/or why? IE. Have you understood the change and why I suggested it?

Ive just had time to take a quick glance but the first thing I see is "AND" has been replaced with"+". My assumption "And" might be for combining a text string rather than math so you added "+"?

[NeoPa]

No. That's not correct. It's a real edge-case as you probably don't expect to deal with negative numbers, but defensive coding generally means following the logic rather than simply getting to approximately the same destination.

Consider what happens with your code, then separately with my code, if you had the values :

[ProdSold]=3
[ProdWaste]=-3

Neither is zero, yet the Sum of the two is zero. The Divide by zero error occurs if the Sum is zero and not only in the case where both values are zero.

This is more important than it appears. You may say "But in my scenario negative values would never occur.", but that would be missing the point.

This code may be copied elsewhere with the confidence that comes from it working reliably here. Wouldn't that be fun. Also, how code is read is important, just as how it's written is. If someone is reading your work later are they to be sent on a wild goose chase trying to determine why you've used two separate tests joined with an AND, or will they see code that immediately makes logical sense because it matches the use later in the same line?

These aren't going to stop your system working but they're opportunities to make you aware of issues that may otherwise trip you up. If you begin things the right way then the learning curve avoids some unnecessary missteps.

[williamson1979]

Makes sense however when I entered your code I got #num, #Div/0 and #Error values with the ProdTotal "0".

Edit: The #Error are in unbound boxes and simply gives the error because the bound boxes are in error, jeez took me a week for that to click.

[williamson1979]

A little detail. ProdTotal is for a finshed product but if nothing is produced it will be 0. Aggregate can still be added or sold regardless so values will be entered. But the subform has Used and Percent of as well that are directly related to ProdTotal.

SELECT tblAggr.AggrID, tblAggr.AggrType, tblAggr.AggrUsed, IIf([ProdSold]=0+[ProdWaste]=0,0,[AggrUsed]/([ProdSold]+[ProdWaste])) AS AggrPerc, tblAggr.AggrSold, tblAggr.AggrAdded
FROM tblProduction INNER JOIN tblAggr ON tblProduction.ProdID = tblAggr.AggrID;

[NeoPa]

I've just looked again at what I suggested in post #9 and realised I mucked it right up. Ignore that one. It's rubbish.

What I SHOULD have said was :
IIf([ProdSold]+[ProdWaste]=0,0,[AggrUsed]/([ProdSold]+[ProdWaste]))
Let's go from there and see if we still have any issues.

Everything I said in posts #12 & #14 was based on my having posted that in #9. If you go through it again it may actually sound like I know what I'm talking about this time (blush).

[williamson1979]

No problem I’ll look at it and try it out.

[williamson1979]

I updated the code Id posted with yours. It displays properly in form view and report view. Thanks

How would I update this code to match the code you gave me a couple post ago? I would think this is correct but in form view it displays 0.00 but in report view it displays Div/0. I have roughly 5 instances of this I'd like to get corrected.

=IIf([txtProdTotal]=0,0,[txtAsphUsed1]/[txtProdTotal])

[NeoPa]

That depend entirely on where this is. In SQL it should be fine. If you have it in VBA anywhere then it could be a problem. Even in the Expression Service, which handles expressions in Controls, it should resolve perfectly safely.

Exactly where are the 5 and can you give an example of one of them please (assuming this isn't the example as I see nothing wrong with it).

[williamson1979]

Is it SQL when it’s entered into the forms property box? That was copied and pasted directly from there once I clicked on the text box, view properties, control source. All 5 are in my main report form. They are all exactly the same except what’s being divided by the ProdTotal. I thought it seemed fine from what I’d already did and what you showed me except yours involved a multi step calculation where this one does not. Just the one you did shows the same in both form and report view. The one I just showed you shows fine in form view but the div error on the report.

It’s definitely not coded on vba though.

[williamson1979]

[AsphUsed1]/[ProdTotal] AS AsphPerc1

Since you mentioned SQL I started looking into the query for the report. The above code apparently did not update with my changes in the form as the one you first helped me on. Is it ok to just edit the SQL directly to show

IIf([txtProdTotal]=0,0,[txtAsphUsed1]/[txtProdTotal])

[williamson1979]

Sorry Neo for 3 post in a row. Since your response to where is the code got my brain spinning I might have fixed it. In the query I entered this

IIf([ProdTotal]=0,0,[HeatNGUsed]/[ProdTotal]) AS HeatNGTon

for 1 of the instances I was speaking of and it now shows 0.0 rather than Div/0 error.

[NeoPa]

All's well that ends well. I much prefer members finding themselves exactly what the problem is. Even if it's been explained to them but they need a little concentration time before they fully appreciate what's what. That's a lesson really learned and will stay with you.