IIf (?) question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

In form I have 4 textboxes: txtField1 to txtField4
I need to sum the textboxes's numeric values.

If all have values, work fine.
If any textboxe don't have value, don't work

I tried some formulas but none resulted.

I would like your help for this question.
Thanks in advance.
an
 
Hi Tina,

I have a similar question to "an" and was hoping you might be able to
elaborate on your formula given my unique conditions. I have three fields
that I want to get an average from, but in some cases one or two of the
fields contains a value that I do not want to include (e.g. 88888 or 99999
which mean no data collected and unknown value, respectively). I would like
the formula to calculate an average of the three columns, but if the values
in the three columns are 88888 or 99999 (they always will never be a combo of
the two) to give me 88888 or 99999 as the output, but if column 2 and or 3
are 88888 or 99999 to not include those columns in the average and use only
the columns containing actual values to calculate the average. Example:

7, 6, 88888 = 6.5
7, 99999, 99999 = 7
88888, 88888, 88888 = 88888
Do you have any suggestions/ideas? Thanks in advance.
 
Now you know why some database gurus warn against "special" values.

and if some are 88888 and some are 99999?

something along these lines either gives the right average or 0 as a
result.

av: IIf([aa]=88,0,[aa])+IIf([bb]=88,0,[bb])+IIf([cc]=88,0,[cc])

You can input this into another query and use something like:

iif([av]=0, "Unknown",[av])

it is easier to end up right if you start out right.
 
Back
Top