Text box to show average (1 Viewer)

vbaInet · Jul 23, 2014

One of your field names are incorrect or have spaces in them.

fat controller · Jul 23, 2014

Here is a copy of the one I am playing with.

vbaInet · Jul 23, 2014

There's something with your form. Creating a new one.

fat controller · Jul 23, 2014

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

All working now, thank you

vbaInet · Jul 23, 2014

Good to know

fat controller · Jul 23, 2014

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?

vbaInet · Jul 23, 2014

Why do you think that's happening? Have a good think.

fat controller · Jul 23, 2014

Because I have stopped it returning a zero?

vbaInet · Jul 23, 2014

Division by zero fat controller

See attached.

fat controller · Jul 23, 2014

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:

Code:

AveOpsDed.SetFocus
Me.Refresh
Me.Ops_Deduct_Ave.Value = AveOpsDed
DoCmd.GoToControl "R1EngDed"

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

vbaInet · Jul 23, 2014

The database I sent you has a fix.

fat controller · Jul 23, 2014

And I could just hide the txtDenom and txtNumer text boxes so it shows only the average in the box above.

Thank you once again

There is quite a lot to this VBA lark, isn't there? But looking at it the other way, it can do a remarkable amount

vbaInet · Jul 23, 2014

That's right. I made them visible so you could understand how the calculations are done.

fat controller · Jul 23, 2014

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)

vbaInet · Jul 23, 2014

No problem!

Did you understand the entire code though?

fat controller · Jul 23, 2014

vbaInet said:
No problem!

Did you understand the entire code though?

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.

vbaInet · Jul 23, 2014

There's more to it than that. Do you understand all the functions used and why? Have a read through and post the lines of code and I'll explain.

fat controller · Jul 26, 2014

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.....

Code:

=IIf(IsNull([EWT1])+IsNull([EWT2])+IsNull([EWT3])+IsNull([EWT4])=-4,Null,IIf([txtDenom]=0,0,[txtNumer]/[txtDenom]))

As I read this:

- 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

vbaInet · Jul 27, 2014

fat controller said:
- 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?

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

fat controller · Jul 27, 2014

Thank you

Just as the great saying - you learn something new every day

Text box to show average (1 Viewer)

AWF VIP

Slightly round the bend..

Attachments

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Attachments

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

AWF VIP

Slightly round the bend..

Similar threads

Users who are viewing this thread