Difficulty writing a function 1

[VickyC]

hi to all

I'm struggling with a function that is giving me grief. Here's the situation:

I have 5 textboxes that each can hold numbers formatted to show 3 decimal places.

[tt]
txtA, txtB, txtC, txtD, txtAVG
4.600 4.550 4.600 4.545 4.575
[/tt]

Values can be entered into any of the first 4 textboxes. txtAVG can display 'greyed' results, but it has enabled = False.

If the first 4 textboxes all have values, then txtAVG must show the average of the 'middle' two values after the top and bottom are discarded (yes, doubles are common!) In the example above, we find the average of 4.550 and 4.600.

If only three (any three!) of the text boxes have values, then txtAVG must show the average of all three.

If only two of the text boxes have values, then txtAVG must show their average.

If only one of the textboxes has a value, then txtAVG must show that value.

I'm feeling rusty with writing this function and would really appreciate help. I'm guessing the function would run in the after_update event of the 4 textboxes.

many thanks
Vicky C.

 

[dhookom]

You can use the Nz() to add the values and IsNull() to determine if a control has a value.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would but something in the tag property, lets say a ?
then something like this untested.

  dim minValue as double
  dim maxValue as double
  dim count as integer
  dim ctrl as access.control
  dim i as integer
  dim total as double
  dim rtn as double
  
  for each ctrl in me.controls
    if ctrl.tag = "?"
      if not isnull(ctrl) then
        i = I + 1
        total = total + ctrl.value
        if I = 1
          minvalue = ctrl.value
          maxvalue = ctrl.value
        else
          if ctrl.value < minvalue then minvalue = ctrl.value
          if ctrl.value > maxValue then maxvalue = ctrl.value
        end if  
    end if
 next ctrl

   if I = 4 then 
       total = total - minvalue - maxvalue
       I = 2
   end if
   if I > 0 then
     rtn = Total/I
   end if
   functionName = rtn

 

[VickyC]

hi - this gives me what I needed to get the function to work. Many thanks.

 

[dhookom]

I think you could also use a control source like:

=(Nz(txtA,0)+Nz(txtB,0)+Nz(txtC,0))/(4+IsNull(txtA)+IsNull(txtB)+IsNull(txtC)+IsNull(txtD))

I would also question why aggregates are being performed across fields rather than across records. This seems a bit un-normalized.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Won't that give an answer of 4.57375 instead of 4.575?

 

[dhookom]

I missed the discarding specification as well as the inclusion of txtD in the numerator. I guess I was too hung up on the normalization issue ;-)

Duane
Hook'D on Access
MS Access MVP