I've just tried putting that line (adjusted for the correct field names) in as the control source on the form on the main DB and I get the same result? Could it be something my PC is doing to the form?
EDIT - no is isn't, I was being a blind old git again and had missed a character in one of the field names
One last wee query - when I create a new record, before anything is put in, the textbox shows #Num! - is there any way to get it to remain blank unless there is data for it to work with?
Ah, so not getting #Num! is just me wanting my cake and eating it as usual - I'd rather put up with that and have it calculating correctly which is is doing now. Besides, I have devised a wee work around - hide the textbox with the formula in it, and then set an After Update on each field that makes up the sum as follows:
By making the textbox with the formula in it (AveOpsDed) not visible, then getting the value in the textbox on view updated, the form looks neat without too much hassle.
Probably not the best way to do it, but it seems to work just fine
I have got a long way to go before I am anywhere near a reasonable standard, but do appreciate your time in helping me learn (and also for not laughing me out of class when I ask silly questions)
I think so - I am still at the stage of reading it through and mentally doing the 'action' in my head if that makes sense?
If I understand it, if all of the fields are null, the result for the numerator should be -4 and the field should display null; if they are not null, then it follows on to take the value from txtNumer and divides it by txtDenom, with both of those fields containing the sum that gives the average of the four fields excluding null values.
Apologies for not coming back to this sooner - a couple of days from hell have left me wondering which way is up (death, tears, blood, sweat - you name it, its all happened). Anyway, back to the subject in hand.....
- If all four fields are Null, then the result returned should be -4, if not then return a null value.
- If the txtDenom is equal to 0, show 0, if not show the result from the calculation tctNumer divided by txtDenom.
- txtDenom is set to calculate absolute values and show a 0 where there is a null, and also handle and calculate any number even if it is a negative value, and to give the result of the sum of the four fields (be they + or -, or if null then a 0)
- txtNumer - calculate absolute values and show the result of the sum of all fields, with the value being 0 where there is a null.
How much did I miss?
EDIT - PS, please don't dedicate too much time or urgency to this, as I am happy to plod along and take my time learning this stuff, so there is no urgency
Nailed it fat controller!
With regards the above, it can actually return -ve values because you're not using the Abs() function. It's only the denominator (txtDenom) that returns absolute values (as you explained).
NB: Don't worry, we can answer to multiple threads